package com.lydia.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionStatus;

import javax.xml.rpc.ServiceException;
import java.io.*;
import java.sql.*;
import java.time.LocalDateTime;
import java.util.*;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.atomic.AtomicBoolean;

/**
 * @author Admin
 */
@Service
public class DataSynService {

    @Autowired
    private PlatformTransactionManager transactionManager;
    List<TransactionStatus> transactionStatuses = Collections.synchronizedList(new ArrayList<TransactionStatus>());
    public void doSyn(Map<String,Object> param) throws ServiceException, SQLException {
        System.out.println("数据同步开始");
        //第一步：根据传入参数获取源数据库连接
        String fromType = (String) param.get("fromType");
        String fromHost = "";
        String fromSchema = (String) param.get("fromSchema");
        String fromDbDriver = "";
        switch (fromType) {
            case "MySQL":
                //mysql
                fromHost = "jdbc:mysql://" + param.get("fromUrl") + ":" + param.get("fromPort") + "/" + param.get("fromDbName") + "?zeroDateTimeBehavior=convertToNull";
                fromDbDriver = "com.mysql.jdbc.Driver";
                param.put("fromSchema",param.get("fromDbName"));
                break;
            case "PostgreSQL":
            case "VastBase":
                fromHost = "jdbc:postgresql://" + param.get("fromUrl") + ":" + param.get("fromPort") + "/" + param.get("fromDbName");
                //schema默认是public，不写的话就默认值public
                if(!"".equals(fromSchema) && fromSchema != null){
                    fromHost = fromHost + "?currentSchema=" + fromSchema;
                }else{
                    fromHost = fromHost + "?currentSchema=public";
                    param.put("fromSchema","public");
                }
                fromDbDriver = "org.postgresql.Driver";
                break;
            case "Oracle":
            case "Oracle10":
                String fromOracleType = (String) param.get("fromOracleType");
                if ("1".equals(fromOracleType)) {
                    fromHost = "jdbc:oracle:thin:@" + param.get("fromUrl") + ":" + param.get("fromPort") + "/" + param.get("fromDbName") ;
                } else {
                    fromHost = "jdbc:oracle:thin:@" + param.get("fromUrl") + ":" + param.get("fromPort") + ":" + param.get("fromDbName") ;
                }
                fromDbDriver = "oracle.jdbc.driver.OracleDriver";
                break;
            default:
                break;
        }
        param.put("fromDbDriver",fromDbDriver);
        param.put("fromHost",fromHost);

        //第二步：根据传入参数获取目标数据库连接
        String toType = (String) param.get("toType");
        String toHost = "";
        String toSchema = (String) param.get("toSchema");
        String toDbDriver = "";
        switch (toType) {
            case "MySQL":
                //mysql
                toHost = "jdbc:mysql://" + param.get("toUrl") + ":" + param.get("toPort") + "/" + param.get("toDbName") ;
                toDbDriver = "com.mysql.jdbc.Driver";
                param.put("toSchema",param.get("toDbName"));
                break;
            case "PostgreSQL":
            case "VastBase":
                toHost = "jdbc:postgresql://" + param.get("toUrl") + ":" + param.get("toPort") + "/" + param.get("toDbName");

                if(!"".equals(toSchema) && toSchema != null){
                    toHost = toHost + "?currentSchema=" + toSchema;
                }else{
                    toHost = toHost + "?currentSchema=public";
                    param.put("toSchema","public");
                }
                toDbDriver = "org.postgresql.Driver";
                break;
            case "Oracle":
            case "Oracle10":
                String toOracleType = (String) param.get("toOracleType");
                if ("1".equals(toOracleType)) {
                    toHost = "jdbc:oracle:thin:@" + param.get("toUrl") + ":" + param.get("toPort") + "/" + param.get("toDbName");
                } else {
                    toHost = "jdbc:oracle:thin:@" + param.get("toUrl") + ":" + param.get("toPort") + ":" + param.get("toDbName");
                }
                toDbDriver = "oracle.jdbc.driver.OracleDriver";
                break;
            case "DM":
                toHost = "jdbc:dm://" +  param.get("toUrl") + ":" + param.get("toPort");
                if(!"".equals(toSchema) && toSchema != null){
                    toHost = toHost + "?Schema=" + toSchema;
                }else{
                   throw new ServiceException("请输入Schema");
                }
                toDbDriver = "dm.jdbc.driver.DmDriver";
                break;
            default:
                break;
        }
        param.put("toHost",toHost);
        param.put("toDbDriver",toDbDriver);

        //第三步：开始迁移 （多线程迁移）
        //获取要迁移的表或者查询语句
        List<String> listTables = new ArrayList<>(16);
        String synSql = (String) param.get("sysnTables");
        if("".equals(synSql)){
            listTables = getAllTables(param);
        }else{
            listTables = Arrays.asList(synSql.split(";"));
        }
        System.out.println("***********");

        //迁移过程中如果有需要迁移触发器，最好是在数据迁移完成之后再迁触发器
        //需要迁移表结构，从源数据库中获取表结构，多线程操作
        // 线程数量
        final Integer threadCountYx = 1;
        //每个线程处理的数据量
        final Integer dataPartionLengthYx = (listTables.size() + threadCountYx - 1) / threadCountYx;
        // 创建多线程处理任务
        ExecutorService pools = Executors.newFixedThreadPool(threadCountYx);
        //线程计数器
        CountDownLatch count = new CountDownLatch(listTables.size());
        AtomicBoolean isError = new AtomicBoolean(false);
        try {
            for (int i = 0; i < threadCountYx; i++) {
                // 每个线程处理的数据
                List<String> threadDatas = new ArrayList<>();
                long limit = dataPartionLengthYx;
                long toSkip = (long) i * dataPartionLengthYx;
                for (String listTable : listTables) {
                    if (toSkip > 0) {
                        toSkip--;
                        continue;
                    }
                    if (limit-- == 0) {
                        break;
                    }
                    threadDatas.add(listTable);
                }
                pools.execute(() -> {
                    try {
                        try {
                            switch (fromType) {
                                case "MySQL":
                                    if (toType.equals(fromType)) {
                                        //mysql --> mysql
                                        excuteFromMysqlToMysql(threadDatas, param);
                                    } else if ("Oracle".equals(toType)) {
                                        //mysql --> oracle
//                                        excuteFromMysqlToOracle(threadDatas, param);

                                    }else if("PostgreSQL".equals(toType) || "VastBase".equals(toType)){
                                        //mysql --> postgresql
                                        excuteFromMysqlToPostgresql(threadDatas, param);
//                                    }else if("VastBase".equals(toType)){
//                                        //oracle --> vastbase  先用postgresql的，如果后续发现变化较大在改
//                                        excuteFromOracleToVastbase(threadDatas, param);
                                    }else {
                                        return;
                                    }
                                    break;
                                case "PostgreSQL":
                                    if (toType.equals(fromType)) {
                                        //postgresql --> postgresql
                                        excuteFromPostgresqlToPostgresql(threadDatas, param);
                                    } else if ("MySQL".equals(toType)) {
                                        //postgresql --> mysql
                                        excuteFromPostgresqlToMysql(threadDatas, param);

                                    }else if("Oracle".equals(toType)){
                                        //postgresql --> oracle
                                    }else if("VastBase".equals(toType)){
                                        excuteFromPostgresqlToPostgresql(threadDatas, param);
                                    }else if("DM".equals(toType)){
                                        excuteFromPostgresqlToDM(threadDatas, param);
                                    }
                                    break;
                                case "VastBase":
                                    if (toType.equals(fromType)) {
                                        //VastBase --> VastBase
                                        excuteFromVastBaseToVastBase(threadDatas, param);
                                    } else if ("MySQL".equals(toType)) {
                                        //postgresql --> mysql
//                                        excuteFromPostgresqlToMysql(threadDatas, param);

                                    }else if("Oracle".equals(toType)){
                                        //postgresql --> oracle
                                    }
                                    break;
                                case "Oracle":
                                case "Oracle10":
                                    if (fromType.contains(toType)) {
                                        //oracle --> oracle
                                        excuteFromOracleToOracle(threadDatas, param);

                                    } else if ("MySQL".equals(toType)) {
                                        //oracle --> mysql
                                        excuteFromOracleToMysql(threadDatas, param);

                                    }else if("PostgreSQL".equals(toType) || "VastBase".equals(toType)){
                                        //oracle --> postgresql
                                        excuteFromOracleToPostgresql(threadDatas, param);
//                                    }else if("VastBase".equals(toType)){
//                                        //oracle --> vastbase  先用postgresql的，如果后续发现变化较大在改
//                                        excuteFromOracleToVastbase(threadDatas, param);
                                    }else{
                                        return;
                                    }
                            }
                        } catch (Throwable e) {
                            e.printStackTrace();
                            isError.set(true);
                        } finally {
                            count.countDown();
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                        isError.set(true);
                    }
                });
            }

        } catch (Throwable e) {
            e.printStackTrace();
            isError.set(true);
        }

        if (!transactionStatuses.isEmpty()) {
            if (isError.get()) {
                transactionStatuses.forEach(s -> transactionManager.rollback(s));
            } else {
                transactionStatuses.forEach(s -> transactionManager.commit(s));
            }
        }

    }

    private void excuteFromPostgresqlToMysql(List<String> threadDatas, Map<String, Object> param) {
        try (Connection sourceConn = getSourceConn(param)) {
            Connection toConn = getToConn(param);
            try {
                List<String> synType = (List) param.get("synType");
                //迁移表结构
                if (synType.contains("1")) {
                    for (String tableName : threadDatas) {
                        //获取表名,如果输入的是sql语句，则取from之后的为表名
                        tableName =  getTableName(tableName) ;
                        //先判断目标数据库是否存在表，如有删除表
                        dropTableIfExist("`" +tableName + "`", param, toConn,0);
                        //获取表结构
                        //从源数据库中获取表结构
                        ResultSet columns;
                        String createTableSql = null;
                        try (Statement stmt = sourceConn.createStatement()) {
                            String selectPostgresTable = "SELECT distinct col.column_name,  d.description,col.udt_name,col.character_maximum_length,col.numeric_precision,col.is_nullable,col.column_default,col.ordinal_position\n" +
                                    "FROM information_schema.columns col\n" +
                                    "JOIN pg_class c ON c.relname = col.table_name and (col.table_schema || '.' || col.table_name)::regclass::oid = c.oid\n" +
                                    "LEFT JOIN pg_description d ON d.objoid = c.oid AND d.objsubid = col.ordinal_position \n" +
                                    "WHERE col.table_schema = '" + getToSelectTableName((String) param.get("fromSchema")).replace(".","")
                                    +  "' and TABLE_NAME = '" + tableName.toLowerCase(Locale.ROOT) + "'  order by col.ordinal_position";
                            columns = stmt.executeQuery(selectPostgresTable);
                            StringBuilder createTableSql1 = new StringBuilder("CREATE TABLE `").append(tableName).append("` (");
                            while (columns.next()) {
                                //列的名称。
                                String columnName = columns.getString(1);
                                //列的数据类型的数据库特定名称，如VARCHAR2, INT, TEXT等。
                                String fromColumnType = columns.getString(3);
                                //列的最大字节数。对于数值类型，这可能是最大精度。
                                int fromDataSize = columns.getInt(4);
                                //对于数值类型，表示小数点右侧的数字位数；对于其他类型，通常为null或0。
                                int fromDecimalDigits = columns.getInt(5);
                                //列是否允许为空
                                String nullable = columns.getString(6).equals("YES") ? "" : " NOT NULL";
                                //列的默认值
                                String defaultValue = columns.getString(7);

                                if(defaultValue != null && !"".equals(defaultValue) && !"NULL".equals(defaultValue)) {
                                    if(defaultValue.contains("::")){
                                        defaultValue = defaultValue.substring(0,defaultValue.indexOf("::"));
                                    }
                                    if(defaultValue.contains("NULL") || defaultValue.contains("nextval") || defaultValue.contains("uuid")){
                                        defaultValue = null;
                                    }else{
                                    }
                                }else if("NULL".equals(defaultValue)  ){
                                    defaultValue = null;
                                }else{
                                    defaultValue = null;
                                }

                                //列的注释或描述。
                                String remarks = columns.getString(2);
                                String toColumnType = "";

                                toColumnType = translateColumnTypeFromPostgresToMysql(fromColumnType, fromDataSize, fromDecimalDigits, 0, nullable, defaultValue);

                                createTableSql1.append("`").append(columnName.toLowerCase(Locale.ROOT)).append("` ").append(toColumnType);

                                if(remarks != null && !"".equals(remarks) ) {
                                    //处理备注中有单引号的 ： 出差类型编号（定义在字典表中：info_type_code='401'） 需要再原来的基础上再加一个单引号做转义
                                    if(remarks.contains("'")){
                                        remarks = remarks.replace("'","''");
                                    }
                                    createTableSql1.append(" COMMENT '").append(remarks).append("' , ");
                                }else{
                                    createTableSql1.append(" , ");
                                }

                            }
                            if (createTableSql1.length() > 0) {
                                createTableSql1.setLength(createTableSql1.length() - 2);
                            }
                            createTableSql1.append(")");

                            //表的注释或描述。
                            String table_comments = "";
                            try (Statement stmt_source = sourceConn.createStatement()) {
                                String selectTableComments = "SELECT d.description\n" +
                                        "FROM information_schema.tables tb\n" +
                                        "JOIN pg_class c ON c.relname = tb.table_name and (tb.table_schema || '.' || tb.table_name)::regclass::oid = c.oid\n" +
                                        "LEFT JOIN pg_description d ON d.objoid = c.oid AND d.objsubid = '0'\n" +
                                        "WHERE tb.table_schema = '" + getToSelectTableName((String) param.get("fromSchema")).replace(".","") + "' AND tb.table_name = '" + tableName.toLowerCase(Locale.ROOT) + "'" ;
                                ResultSet rs = stmt_source.executeQuery(selectTableComments);
                                if (rs.next()) {
                                    table_comments = rs.getString(1);
                                    if(table_comments != null && !"".equals(table_comments)) {
                                        createTableSql1.append(" COMMENT = '").append(table_comments).append("';");
                                    }else{
                                        createTableSql1.append(";");
                                    }
                                }
                            } catch (SQLException e) {
                                System.out.println("获取表注释失败");
                            }

                            createTableSql = createTableSql1.toString();

                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                        //在目标库中创建表
                        try (Statement stmt1 = toConn.createStatement()) {
                            stmt1.execute(createTableSql);
                            System.out.println("从postgres迁移到mysql的表：" + tableName + "表结构迁移成功！");
                        } catch (SQLException e) {
                            e.printStackTrace();
                            System.out.println("从postgres迁移到mysql的表：" + tableName + "表结构迁移失败！" + e.getMessage());
                        }
                    }
                }
                //迁移索引
                if (synType.contains("6")) {
                    for (String tableName : threadDatas) {
                        //获取表名,如果输入的是sql语句，则取from之后的为表名
                        tableName = getTableName(tableName);
                        //先判断目标数据库是否存在该索引，如有删除表
                        ResultSet v_indexes;
                        try (Statement stmt = toConn.createStatement()) {
                            String sql = "SELECT index_name FROM information_schema.statistics  " +
                                    "WHERE TABLE_SCHEMA = '" + getToSelectTableName((String) param.get("toSchema")).replace(".", "") + "'" +
                                    " AND  table_name = '" + tableName.toUpperCase(Locale.ROOT) + "'" +
                                    " group by INDEX_name\n";
                            v_indexes = stmt.executeQuery(sql);
                            while (v_indexes.next()) {
                                String dropIndexSql = "ALTER TABLE " + getToSelectTableName((String) param.get("toSchema") ) + tableName.toLowerCase(Locale.ROOT)
                                        + " DROP INDEX "  + v_indexes.getString(1).toLowerCase(Locale.ROOT);
                                System.out.println(dropIndexSql);
                                try (PreparedStatement dropIndexStatement = toConn.prepareStatement(dropIndexSql)) {
                                    dropIndexStatement.execute();
                                } catch (SQLException e) {
                                    // Handle exception gracefully (e.g. log message and move on)
                                    System.err.println("删除" + tableName + "." + v_indexes.getString("INDEX_NAME") + "失败： " + e.getMessage());
                                }
                            }
                        }
                        //获取目标数据库的索引，写入目标数据库
                        DatabaseMetaData metaData = sourceConn.getMetaData();
                        // 查询指定表的索引
                        ResultSet indexes;
                        try (Statement stmt = sourceConn.createStatement()) {
                            String sql = "SELECT c.relname , i.relname, pg_get_indexdef(i.oid) \n" +
                                    "    FROM pg_class c JOIN pg_index ix ON c.oid = ix.indrelid \n" +
                                    "    JOIN pg_class i ON i.oid = ix.indexrelid \n" +
                                    "    WHERE c.relkind = 'r' AND i.relkind = 'i' " +
                                    "AND c.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = '" + getToSelectTableName((String) param.get("fromSchema")).replace(".","") + "')" +
                                    " AND  c.relname = '" + tableName + "'" ;
                            indexes = stmt.executeQuery(sql);
                            while (indexes.next()) {
                                String table_name = indexes.getString(1);
                                String index_name = indexes.getString(2);
                                String indexDDl = indexes.getString(3).toUpperCase(Locale.ROOT);
                                String v_indexType = indexDDl.substring(indexDDl.indexOf("CREATE ")+7,indexDDl.indexOf("INDEX") +5) ;
                                String v_indexColummn = indexDDl.substring(indexDDl.indexOf("("),indexDDl.indexOf(")")+1 );
                                v_indexColummn = v_indexColummn.replace("\"","");


                                StringBuffer v_index = new StringBuffer();
                                v_index.append("ALTER TABLE `").append(getToSelectTableName((String) param.get("toSchema")).replace(".",""))
                                        .append("`.`").append(table_name).append("` ").append(" ADD ").append(v_indexType).append(" `").append(index_name).append("` ")
                                        .append(v_indexColummn).append(" USING BTREE;");
                                // 在MySQL中执行创建索引的SQL
                                try (Statement mysqlStmt = toConn.createStatement()) {
                                    System.out.println(v_index);
                                    mysqlStmt.executeUpdate(v_index.toString().toLowerCase(Locale.ROOT));
                                    System.out.println(tableName + "表的索引"+ v_index + "迁移成功！");
                                } catch (SQLException e) {
                                    System.err.println(tableName + "表的索引迁移失败！" + v_index + "' in MySQL: " + e.getMessage());
                                }
                            }
                        }
                    }
                }
                if (synType.contains("2")) {
                    transData(threadDatas, (String) param.get("fromSchema"), (String) param.get("toSchema"), (String) param.get("fromType"), sourceConn, toConn);
                }
                //迁移视图
                if(synType.contains("3")){
                    for (String tableName : threadDatas) {
                        //获取表名,如果输入的是sql语句，则取from之后的为表名
                        tableName = getTableName(tableName);
                        //先判断目标数据库是否存在视图，如有删除视图
                        dropTableIfExist(tableName, param, toConn,1);
                        System.out.println("当前迁移的视图为：" + tableName + "开始迁移时间：" + LocalDateTime.now());
                        //从源数据库中获取表结构
                        ResultSet columns;
                        try (Statement stmt = sourceConn.createStatement()) {
                            columns = stmt.executeQuery("SELECT viewname,definition\n" +
                                    "FROM pg_catalog.pg_views\n" +
                                    "WHERE schemaname = '" + getFromSelectTableName((String) param.get("fromSchema")).replace(".","") + "'" +
                                    "and viewname = '" + tableName + "'");
                            StringBuilder createTableSql1 = new StringBuilder("CREATE  OR REPLACE VIEW  ");

                            while (columns.next()) {
                                //视图名称。
                                String viewName = columns.getString(1);
                                //视图的定义。
                                String viewDefinition= columns.getString(2);
                                viewDefinition = viewDefinition.replace("\"","").replace(getFromSelectTableName((String) param.get("fromSchema")),"");
                                //针对视图中用到!() 进行的单独处理。v_company_part
                                viewDefinition = viewDefinition.replace("!(","not (").replace("! (","not (");
                                //这里应该还有一个语法兼容问题，遇到在处理
                                createTableSql1.append(viewName).append(" as ").append(viewDefinition).append(";");
                            }

                            try (Statement stmt1 = toConn.createStatement()) {
                                stmt1.execute(createTableSql1.toString());
                                System.out.println("从postgres迁移到postgres的视图：" + tableName + "迁移成功！视图迁移结束时间：" + LocalDateTime.now() );
                            } catch (SQLException e) {
                                e.printStackTrace();
                                System.out.println("从postgres迁移到postgres的视图：" + tableName + "迁移失败！有语法兼容问题，请自行修改。" + e.getMessage());
                            }
                        } catch (SQLException e) {
                            System.out.println("获取视图定义失败或未查询到该视图:" + tableName + e.getMessage());
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                sourceConn.close();
                toConn.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }



    private List<String> getAllTables(Map<String, Object> param) throws SQLException, ServiceException {
        Connection sourceConn = getSourceConn(param);
        String fromType = (String) param.get("fromType");
        String v_tableName = "";
        List<String> synType = (List) param.get("synType");
        switch (fromType){
            case "MySQL":
            case "PostgreSQL":
            case "VastBase":
                if(synType.contains("1") || synType.contains("6") || synType.contains("2")){
                    v_tableName = "SELECT table_name from information_schema.TABLES where table_schema = '" + param.get("fromSchema") +  "' and table_type = 'BASE TABLE'";
                }else if(synType.contains("3")){
                    v_tableName = "SELECT table_name from information_schema.TABLES where table_schema = '" + param.get("fromSchema") +  "' and table_type = 'VIEW'";
                }
                break;
            case "Oracle":
                v_tableName = "select table_name from user_tables";
                if(synType.contains("1")){
                    v_tableName = "select table_name from user_tables";
                }else if("3".equals(synType)){
                    v_tableName = "select view_name from user_views";
                }
                break;
            default:
                throw new ServiceException("未知源数据库类型！！！") ;
        }
        List<String> tables = new ArrayList<>(16);
        try {
            try (PreparedStatement sourceStmt = sourceConn.prepareStatement(v_tableName)) {

                ResultSet resultSet = sourceStmt.executeQuery();
                while (resultSet.next()) {
                    tables.add(resultSet.getString(1));
                }
            }
        }catch (Exception e){
            throw new ServiceException("获取表名失败！！！") ;
        }finally {
            sourceConn.close();
        }
        return tables;
    }

    /**
     * vastbase --> vastbase
     * @param threadDatas  迁移的表或者sql语句
     * @param param 页面写入的参数
     * @throws SQLException
     */
    private void excuteFromVastBaseToVastBase(List<String> threadDatas, Map<String, Object> param) throws SQLException {
        Connection sourceConn = getSourceConn(param);
        Connection toConn = getToConn(param);
        try {
            List<String> synType = (List) param.get("synType");
            //迁移表结构
            if (synType.contains("1")) {
                for (String tableName : threadDatas) {
                    //获取表名,如果输入的是sql语句，则取from之后的为表名
                    tableName = getTableName(tableName);
                    //先判断目标数据库是否存在表，如有删除表
                    dropTableIfExist(tableName, param, toConn,0);
                    //获取表结构
                    //从源数据库中获取表结构
                    ResultSet columns;
                    String createTableSql = null;
                    try (Statement stmt = sourceConn.createStatement()) {
                        columns = stmt.executeQuery("show CREATE table " + param.get("fromSchema") + "." + tableName);
                        while (columns.next()) {
                            createTableSql = columns.getString(2);
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                    createTableSql = createTableSql.substring(createTableSql.indexOf("CREATE TABLE"));
                    //在目标库中创建表
                    try (Statement stmt1 = toConn.createStatement()) {
                        stmt1.execute(createTableSql);
                        System.out.println("从vastbase迁移到vastbase的表：" + tableName + "表结构迁移成功！");
                    } catch (SQLException e) {
                        e.printStackTrace();
                        System.out.println("从vastbase迁移到vastbase的表：" + tableName + "表结构迁移失败！" + e.getMessage());
                    }
                }
            }
            //迁移索引
            if (synType.contains("6")) {
                //因为迁移表的时候已经把索引迁移了，所以这里不需要迁移索引
            }
            if (synType.contains("2")) {
                transData(threadDatas, (String) param.get("fromSchema"), (String) param.get("toSchema"), (String) param.get("fromType"), sourceConn, toConn);
            }
            //
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    /**
     * postgresql --> vastbase
     * 实际上这段是从16版本的pg数据库向海量数据库迁移的代码
     * 海量数据库也是以postgresql数据库为底层的，所以实际上这段代码是迁移pg数据库到pg数据库的
     * @param threadDatas
     * @param param
     */
    private void excuteFromPostgresqlToPostgresql(List<String> threadDatas, Map<String, Object> param) {
        try (Connection sourceConn = getSourceConn(param)) {
            Connection toConn = getToConn(param);
            try {
                List<String> synType = (List) param.get("synType");
                //迁移表结构
                if (synType.contains("1")) {
                    for (String tableName : threadDatas) {
                        //获取表名,如果输入的是sql语句，则取from之后的为表名
                        tableName = getTableName(tableName);
                        //先判断目标数据库是否存在表，如有删除表
                        dropTableIfExist(tableName, param, toConn,0);
                        //获取表结构
                        //从源数据库中获取表结构
                        ResultSet columns;
                        String createTableSql = null;
                        String v_commentsSql = null;
                        try (Statement stmt = sourceConn.createStatement()) {
                            String selectPostgresTable = "SELECT distinct col.column_name,  d.description,col.udt_name,col.character_maximum_length,col.numeric_precision,col.is_nullable,col.column_default,col.ordinal_position\n" +
                                    "FROM information_schema.columns col\n" +
                                    "JOIN pg_class c ON c.relname = col.table_name and (col.table_schema || '.' || col.table_name)::regclass::oid = c.oid\n" +
                                    "LEFT JOIN pg_description d ON d.objoid = c.oid AND d.objsubid = col.ordinal_position \n" +
                                    "WHERE col.table_schema = '" + getToSelectTableName((String) param.get("fromSchema")).replace(".","")
                                    +  "' and TABLE_NAME = '" + tableName.toLowerCase(Locale.ROOT) + "'  order by col.ordinal_position";
                            columns = stmt.executeQuery(selectPostgresTable);
                            StringBuilder createTableSql1 = new StringBuilder("CREATE TABLE ").append(param.get("toSchema").toString()).append(".").append(tableName).append(" (");
                            String comments = "COMMENT ON COLUMN  " + tableName + ".";
                            StringBuilder commentsSql = new StringBuilder();
                            while (columns.next()) {
                                //列的名称。
                                String columnName = columns.getString(1);
                                //列的数据类型的数据库特定名称，如VARCHAR2, INT, TEXT等。
                                String fromColumnType = columns.getString(3);
                                //列的最大字节数。对于数值类型，这可能是最大精度。
                                int fromDataSize = columns.getInt(4);
                                //对于数值类型，表示小数点右侧的数字位数；对于其他类型，通常为null或0。
                                int fromDecimalDigits = columns.getInt(5);
                                //列是否允许为空
                                String nullable = columns.getString(6).equals("YES") ? "" : " NOT NULL";
                                //列的默认值
                                String defaultValue = columns.getString(7);
                                if(defaultValue != null && !"".equals(defaultValue) && !"NULL".equals(defaultValue)) {
                                    if(defaultValue.contains("NULL")){
                                        defaultValue = null;
                                    }else if(defaultValue.contains("''")){
                                      defaultValue = defaultValue.replace("''","0");
                                    }else{
                                    }
                                }else if("NULL".equals(defaultValue)  ){
                                    defaultValue = null;
                                }else{
                                    defaultValue = null;
                                }

                                //列的注释或描述。
                                String remarks = columns.getString(2);
                                String toColumnType = "";
                                if("PostgreSQL".equals(param.get("toType")) || "VastBase".equals(param.get("toType"))) {
                                    //pg VastBase
                                    toColumnType = translateColumnTypeFromPostgresToPostgresql(fromColumnType, fromDataSize, fromDecimalDigits, 0, nullable, defaultValue);
                                }

                                createTableSql1.append("\"").append(columnName.toLowerCase(Locale.ROOT)).append("\" ").append(toColumnType);

                                createTableSql1.append(", ");
                                if(remarks != null && !"".equals(remarks) ) {
                                    //处理备注中有单引号的 ： 出差类型编号（定义在字典表中：info_type_code='401'） 需要再原来的基础上再加一个单引号做转义
                                    if(remarks.contains("'")){
                                        remarks = remarks.replace("'","''");
                                    }
                                    commentsSql.append(comments).append(columnName.toLowerCase(Locale.ROOT)).append(" IS '" + remarks + "';");
                                }
                            }
                            //表的注释或描述。
                            String table_comments = "";
                            try (Statement stmt_source = sourceConn.createStatement()) {
                                String selectTableComments = "SELECT d.description\n" +
                                        "FROM information_schema.tables tb\n" +
                                        "JOIN pg_class c ON c.relname = tb.table_name and (tb.table_schema || '.' || tb.table_name)::regclass::oid = c.oid\n" +
                                        "LEFT JOIN pg_description d ON d.objoid = c.oid AND d.objsubid = '0'\n" +
                                        "WHERE tb.table_schema = '" + getToSelectTableName((String) param.get("fromSchema")).replace(".","") + "' AND tb.table_name = '" + tableName.toLowerCase(Locale.ROOT) + "'" ;
                                ResultSet rs = stmt_source.executeQuery(selectTableComments);
                                if (rs.next()) {
                                    table_comments = rs.getString(1);
                                    if(table_comments != null && !"".equals(table_comments)) {
                                        commentsSql.append("COMMENT ON TABLE ").append(param.get("toSchema").toString()).append(".").append(tableName).append(" IS '" + table_comments + "';");
                                    }
                                }
                            } catch (SQLException e) {
                                System.out.println("获取表注释失败");
                            }

                            // Remove trailing comma and space
                            if (createTableSql1.length() > 0) {
                                createTableSql1.setLength(createTableSql1.length() - 2);
                            }
                            createTableSql1.append(") ;");
                            createTableSql = createTableSql1.toString();
                            v_commentsSql = commentsSql.toString();

                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                        //在目标库中创建表
                        try (Statement stmt1 = toConn.createStatement()) {
                            stmt1.execute(createTableSql);
                            stmt1.execute(v_commentsSql);
                            System.out.println("从postgres迁移到postgres的表：" + tableName + "表结构迁移成功！");
                        } catch (SQLException e) {
                            e.printStackTrace();
                            System.out.println("从postgres迁移到postgres的表：" + tableName + "表结构迁移失败！" + e.getMessage());
                        }
                    }
                }
                //迁移索引
                if (synType.contains("6")) {
                    for (String tableName : threadDatas) {
                        //获取表名,如果输入的是sql语句，则取from之后的为表名
                        tableName = getTableName(tableName);
                        //先判断目标数据库是否存在该索引，如有删除表
                        DatabaseMetaData toMetaData1 = toConn.getMetaData();
                        ResultSet toIndexs = toMetaData1.getIndexInfo(param.get("toDbName").toString(), null, tableName, false, true);

                        while (toIndexs.next()) {
                            String dropIndexSql = " DROP INDEX " + param.get("toSchema") + "." +toIndexs.getString("INDEX_NAME").toLowerCase(Locale.ROOT);
                            System.out.println(dropIndexSql);
                            try (PreparedStatement dropIndexStatement = toConn.prepareStatement(dropIndexSql)) {
                                dropIndexStatement.execute();
                            } catch (SQLException e) {
                                // Handle exception gracefully (e.g. log message and move on)
                                System.err.println("删除" +  tableName + "." + toIndexs.getString("INDEX_NAME") + "失败： " + e.getMessage());
                            }
                        }
                        //获取目标数据库的索引，写入目标数据库
                        DatabaseMetaData metaData = sourceConn.getMetaData();
                        // 查询指定表的索引
                        ResultSet indexes;
                        try (Statement stmt = sourceConn.createStatement()) {
                            String sql = "SELECT " +
                                    "    c2.relname AS index_name," +
                                    "    pg_get_indexdef(i.indexrelid) AS index_def " +
                                    "FROM  pg_class c " +
                                    "JOIN pg_namespace n ON n.oid = c.relnamespace " +
                                    "JOIN pg_index i ON c.oid = i.indrelid " +
                                    "JOIN pg_class c2 ON c2.oid = i.indexrelid " +
                                    "WHERE n.nspname = '" + getToSelectTableName((String) param.get("fromSchema")).replace(".","") + "'" +
                                    " AND  c.relname = '" + tableName + "'" ;
                            indexes = stmt.executeQuery(sql);
                            while (indexes.next()) {
                                String indexDDl = indexes.getString(2);
                                indexDDl =  indexDDl.replace(param.get("fromSchema").toString(),param.get("toSchema").toString()).toLowerCase(Locale.ROOT);
                                // 在MySQL中执行创建索引的SQL
                                try (Statement mysqlStmt = toConn.createStatement()) {
                                    System.out.println(indexDDl);
                                    mysqlStmt.executeUpdate(indexDDl);
                                    System.out.println(tableName + "表的索引"+ indexDDl + "迁移成功！");
                                } catch (SQLException e) {
                                    System.err.println(tableName + "表的索引迁移失败！" + indexDDl + "' in MySQL: " + e.getMessage());
                                }
                            }
                        }
                    }
                }
                if (synType.contains("2")) {
                    transData(threadDatas, (String) param.get("fromSchema"), (String) param.get("toSchema"), (String) param.get("fromType"), sourceConn, toConn);
                }
                //迁移视图
                if(synType.contains("3")){
                    for (String tableName : threadDatas) {
                        //获取表名,如果输入的是sql语句，则取from之后的为表名
                        tableName = getTableName(tableName);
                        //先判断目标数据库是否存在视图，如有删除视图
                        dropTableIfExist(tableName, param, toConn,1);
                        System.out.println("当前迁移的视图为：" + tableName + "开始迁移时间：" + LocalDateTime.now());
                        //从源数据库中获取表结构
                        ResultSet columns;
                        try (Statement stmt = sourceConn.createStatement()) {
                            columns = stmt.executeQuery("SELECT viewname,definition\n" +
                                    "FROM pg_catalog.pg_views\n" +
                                    "WHERE schemaname = '" + getFromSelectTableName((String) param.get("fromSchema")).replace(".","") + "'" +
                                    "and viewname = '" + tableName + "'");
                            StringBuilder createTableSql1 = new StringBuilder("CREATE  OR REPLACE VIEW  ");

                            while (columns.next()) {
                                //视图名称。
                                String viewName = columns.getString(1);
                                //视图的定义。
                                String viewDefinition= columns.getString(2);
                                viewDefinition = viewDefinition.replace("\"","").replace(getFromSelectTableName((String) param.get("fromSchema")),"");
                                //针对视图中用到!() 进行的单独处理。v_company_part
                                viewDefinition = viewDefinition.replace("!(","not (").replace("! (","not (");
                                //这里应该还有一个语法兼容问题，遇到在处理
                                createTableSql1.append(viewName).append(" as ").append(viewDefinition).append(";");
                            }

                            try (Statement stmt1 = toConn.createStatement()) {
                                stmt1.execute(createTableSql1.toString());
                                System.out.println("从postgres迁移到postgres的视图：" + tableName + "迁移成功！视图迁移结束时间：" + LocalDateTime.now() );
                            } catch (SQLException e) {
                                e.printStackTrace();
                                System.out.println("从postgres迁移到postgres的视图：" + tableName + "迁移失败！有语法兼容问题，请自行修改。" + e.getMessage());
                            }
                        } catch (SQLException e) {
                            System.out.println("获取视图定义失败或未查询到该视图:" + tableName + e.getMessage());
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                sourceConn.close();
                toConn.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }


    /**
     * postgresql --> 达梦数据库
     * 实际上这段是从16版本的pg数据库向海量数据库迁移的代码
     * 海量数据库也是以postgresql数据库为底层的，所以实际上这段代码是迁移pg数据库到pg数据库的
     * @param threadDatas
     * @param param
     */
    private void excuteFromPostgresqlToDM(List<String> threadDatas, Map<String, Object> param) {
        try (Connection sourceConn = getSourceConn(param)) {
            Connection toConn = getToConn(param);
            try {
                List<String> synType = (List) param.get("synType");
                //迁移表结构
//                if (synType.contains("1")) {
//                    for (String tableName : threadDatas) {
//                        //获取表名,如果输入的是sql语句，则取from之后的为表名
//                        tableName = getTableName(tableName);
//                        //先判断目标数据库是否存在表，如有删除表
//                        dropTableIfExist(tableName, param, toConn,0);
//                        //获取表结构
//                        //从源数据库中获取表结构
//                        ResultSet columns;
//                        String createTableSql = null;
//                        String v_commentsSql = null;
//                        try (Statement stmt = sourceConn.createStatement()) {
//                            String selectPostgresTable = "SELECT distinct col.column_name,  d.description,col.udt_name,col.character_maximum_length,col.numeric_precision,col.is_nullable,col.column_default,col.ordinal_position\n" +
//                                    "FROM information_schema.columns col\n" +
//                                    "JOIN pg_class c ON c.relname = col.table_name and (col.table_schema || '.' || col.table_name)::regclass::oid = c.oid\n" +
//                                    "LEFT JOIN pg_description d ON d.objoid = c.oid AND d.objsubid = col.ordinal_position \n" +
//                                    "WHERE col.table_schema = '" + getToSelectTableName((String) param.get("fromSchema")).replace(".","")
//                                    +  "' and TABLE_NAME = '" + tableName.toLowerCase(Locale.ROOT) + "'  order by col.ordinal_position";
//                            columns = stmt.executeQuery(selectPostgresTable);
//                            StringBuilder createTableSql1 = new StringBuilder("CREATE TABLE ").append(param.get("toSchema").toString()).append(".").append(tableName).append(" (");
//                            String comments = "COMMENT ON COLUMN  " + tableName + ".";
//                            StringBuilder commentsSql = new StringBuilder();
//                            while (columns.next()) {
//                                //列的名称。
//                                String columnName = columns.getString(1);
//                                //列的数据类型的数据库特定名称，如VARCHAR2, INT, TEXT等。
//                                String fromColumnType = columns.getString(3);
//                                //列的最大字节数。对于数值类型，这可能是最大精度。
//                                int fromDataSize = columns.getInt(4);
//                                //对于数值类型，表示小数点右侧的数字位数；对于其他类型，通常为null或0。
//                                int fromDecimalDigits = columns.getInt(5);
//                                //列是否允许为空
//                                String nullable = columns.getString(6).equals("YES") ? "" : " NOT NULL";
//                                //列的默认值
//                                String defaultValue = columns.getString(7);
//                                if(defaultValue != null && !"".equals(defaultValue) && !"NULL".equals(defaultValue)) {
//                                    if(defaultValue.contains("NULL")){
//                                        defaultValue = null;
//                                    }else if(defaultValue.contains("''")){
//                                        defaultValue = defaultValue.replace("''","0");
//                                    }else{
//                                    }
//                                }else if("NULL".equals(defaultValue)  ){
//                                    defaultValue = null;
//                                }else{
//                                    defaultValue = null;
//                                }
//
//                                //列的注释或描述。
//                                String remarks = columns.getString(2);
//                                String toColumnType = "";
//                                if("PostgreSQL".equals(param.get("toType")) || "VastBase".equals(param.get("toType"))) {
//                                    //pg VastBase
//                                    toColumnType = translateColumnTypeFromPostgresToPostgresql(fromColumnType, fromDataSize, fromDecimalDigits, 0, nullable, defaultValue);
//                                }
//
//                                createTableSql1.append("\"").append(columnName.toLowerCase(Locale.ROOT)).append("\" ").append(toColumnType);
//
//                                createTableSql1.append(", ");
//                                if(remarks != null && !"".equals(remarks) ) {
//                                    //处理备注中有单引号的 ： 出差类型编号（定义在字典表中：info_type_code='401'） 需要再原来的基础上再加一个单引号做转义
//                                    if(remarks.contains("'")){
//                                        remarks = remarks.replace("'","''");
//                                    }
//                                    commentsSql.append(comments).append(columnName.toLowerCase(Locale.ROOT)).append(" IS '" + remarks + "';");
//                                }
//                            }
//                            //表的注释或描述。
//                            String table_comments = "";
//                            try (Statement stmt_source = sourceConn.createStatement()) {
//                                String selectTableComments = "SELECT d.description\n" +
//                                        "FROM information_schema.tables tb\n" +
//                                        "JOIN pg_class c ON c.relname = tb.table_name and (tb.table_schema || '.' || tb.table_name)::regclass::oid = c.oid\n" +
//                                        "LEFT JOIN pg_description d ON d.objoid = c.oid AND d.objsubid = '0'\n" +
//                                        "WHERE tb.table_schema = '" + getToSelectTableName((String) param.get("fromSchema")).replace(".","") + "' AND tb.table_name = '" + tableName.toLowerCase(Locale.ROOT) + "'" ;
//                                ResultSet rs = stmt_source.executeQuery(selectTableComments);
//                                if (rs.next()) {
//                                    table_comments = rs.getString(1);
//                                    if(table_comments != null && !"".equals(table_comments)) {
//                                        commentsSql.append("COMMENT ON TABLE ").append(param.get("toSchema").toString()).append(".").append(tableName).append(" IS '" + table_comments + "';");
//                                    }
//                                }
//                            } catch (SQLException e) {
//                                System.out.println("获取表注释失败");
//                            }
//
//                            // Remove trailing comma and space
//                            if (createTableSql1.length() > 0) {
//                                createTableSql1.setLength(createTableSql1.length() - 2);
//                            }
//                            createTableSql1.append(") ;");
//                            createTableSql = createTableSql1.toString();
//                            v_commentsSql = commentsSql.toString();
//
//                        } catch (Exception e) {
//                            e.printStackTrace();
//                        }
//                        //在目标库中创建表
//                        try (Statement stmt1 = toConn.createStatement()) {
//                            stmt1.execute(createTableSql);
//                            stmt1.execute(v_commentsSql);
//                            System.out.println("从postgres迁移到postgres的表：" + tableName + "表结构迁移成功！");
//                        } catch (SQLException e) {
//                            e.printStackTrace();
//                            System.out.println("从postgres迁移到postgres的表：" + tableName + "表结构迁移失败！" + e.getMessage());
//                        }
//                    }
//                }
                //迁移索引
//                if (synType.contains("6")) {
//                    for (String tableName : threadDatas) {
//                        //获取表名,如果输入的是sql语句，则取from之后的为表名
//                        tableName = getTableName(tableName);
//                        //先判断目标数据库是否存在该索引，如有删除表
//                        DatabaseMetaData toMetaData1 = toConn.getMetaData();
//                        ResultSet toIndexs = toMetaData1.getIndexInfo(param.get("toDbName").toString(), null, tableName, false, true);
//
//                        while (toIndexs.next()) {
//                            String dropIndexSql = " DROP INDEX " + param.get("toSchema") + "." +toIndexs.getString("INDEX_NAME").toLowerCase(Locale.ROOT);
//                            System.out.println(dropIndexSql);
//                            try (PreparedStatement dropIndexStatement = toConn.prepareStatement(dropIndexSql)) {
//                                dropIndexStatement.execute();
//                            } catch (SQLException e) {
//                                // Handle exception gracefully (e.g. log message and move on)
//                                System.err.println("删除" +  tableName + "." + toIndexs.getString("INDEX_NAME") + "失败： " + e.getMessage());
//                            }
//                        }
//                        //获取目标数据库的索引，写入目标数据库
//                        DatabaseMetaData metaData = sourceConn.getMetaData();
//                        // 查询指定表的索引
//                        ResultSet indexes;
//                        try (Statement stmt = sourceConn.createStatement()) {
//                            String sql = "SELECT " +
//                                    "    c2.relname AS index_name," +
//                                    "    pg_get_indexdef(i.indexrelid) AS index_def " +
//                                    "FROM  pg_class c " +
//                                    "JOIN pg_namespace n ON n.oid = c.relnamespace " +
//                                    "JOIN pg_index i ON c.oid = i.indrelid " +
//                                    "JOIN pg_class c2 ON c2.oid = i.indexrelid " +
//                                    "WHERE n.nspname = '" + getToSelectTableName((String) param.get("fromSchema")).replace(".","") + "'" +
//                                    " AND  c.relname = '" + tableName + "'" ;
//                            indexes = stmt.executeQuery(sql);
//                            while (indexes.next()) {
//                                String indexDDl = indexes.getString(2);
//                                indexDDl =  indexDDl.replace(param.get("fromSchema").toString(),param.get("toSchema").toString()).toLowerCase(Locale.ROOT);
//                                // 在MySQL中执行创建索引的SQL
//                                try (Statement mysqlStmt = toConn.createStatement()) {
//                                    System.out.println(indexDDl);
//                                    mysqlStmt.executeUpdate(indexDDl);
//                                    System.out.println(tableName + "表的索引"+ indexDDl + "迁移成功！");
//                                } catch (SQLException e) {
//                                    System.err.println(tableName + "表的索引迁移失败！" + indexDDl + "' in MySQL: " + e.getMessage());
//                                }
//                            }
//                        }
//                    }
//                }
                if (synType.contains("2")) {
                    transData(threadDatas, (String) param.get("fromSchema"), (String) param.get("toSchema"), (String) param.get("fromType"), sourceConn, toConn);
                }
                //迁移视图
//                if(synType.contains("3")){
//                    for (String tableName : threadDatas) {
//                        //获取表名,如果输入的是sql语句，则取from之后的为表名
//                        tableName = getTableName(tableName);
//                        //先判断目标数据库是否存在视图，如有删除视图
//                        dropTableIfExist(tableName, param, toConn,1);
//                        System.out.println("当前迁移的视图为：" + tableName + "开始迁移时间：" + LocalDateTime.now());
//                        //从源数据库中获取表结构
//                        ResultSet columns;
//                        try (Statement stmt = sourceConn.createStatement()) {
//                            columns = stmt.executeQuery("SELECT viewname,definition\n" +
//                                    "FROM pg_catalog.pg_views\n" +
//                                    "WHERE schemaname = '" + getFromSelectTableName((String) param.get("fromSchema")).replace(".","") + "'" +
//                                    "and viewname = '" + tableName + "'");
//                            StringBuilder createTableSql1 = new StringBuilder("CREATE  OR REPLACE VIEW  ");
//
//                            while (columns.next()) {
//                                //视图名称。
//                                String viewName = columns.getString(1);
//                                //视图的定义。
//                                String viewDefinition= columns.getString(2);
//                                viewDefinition = viewDefinition.replace("\"","").replace(getFromSelectTableName((String) param.get("fromSchema")),"");
//                                //针对视图中用到!() 进行的单独处理。v_company_part
//                                viewDefinition = viewDefinition.replace("!(","not (").replace("! (","not (");
//                                //这里应该还有一个语法兼容问题，遇到在处理
//                                createTableSql1.append(viewName).append(" as ").append(viewDefinition).append(";");
//                            }
//
//                            try (Statement stmt1 = toConn.createStatement()) {
//                                stmt1.execute(createTableSql1.toString());
//                                System.out.println("从postgres迁移到postgres的视图：" + tableName + "迁移成功！视图迁移结束时间：" + LocalDateTime.now() );
//                            } catch (SQLException e) {
//                                e.printStackTrace();
//                                System.out.println("从postgres迁移到postgres的视图：" + tableName + "迁移失败！有语法兼容问题，请自行修改。" + e.getMessage());
//                            }
//                        } catch (SQLException e) {
//                            System.out.println("获取视图定义失败或未查询到该视图:" + tableName + e.getMessage());
//                        } catch (Exception e) {
//                            e.printStackTrace();
//                        }
//                    }
//                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                sourceConn.close();
                toConn.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    /**
     * mysql --> mysql
     * @param threadDatas 迁移的表或者sql语句
     * @param param 页面写入的参数
     * @throws SQLException
     */
    private void excuteFromMysqlToMysql(List<String> threadDatas, Map<String, Object> param) throws SQLException {
        Connection sourceConn = getSourceConn(param);
        Connection toConn = getToConn(param);
        try {
            List<String> synType = (List) param.get("synType");
            //迁移表结构
            if (synType.contains("1")) {
                for (String tableName : threadDatas) {
                    //获取表名,如果输入的是sql语句，则取from之后的为表名
                    tableName = getTableName(tableName);
                    //先判断目标数据库是否存在表，如有删除表
                    dropTableIfExist(tableName,param,toConn,0);
                    //获取表结构
                    //从源数据库中获取表结构
                    ResultSet columns;
                    String createTableSql = null;
                    try (Statement stmt = sourceConn.createStatement()) {
                        columns = stmt.executeQuery("show CREATE table " + param.get("fromDbName") + "." + tableName);
                        while (columns.next()) {
                            createTableSql = columns.getString(2);
                        }
                    }catch (Exception e){
                        e.printStackTrace();
                    }
                    //在目标库中创建表
                    try (Statement stmt1 = toConn.createStatement()) {
                            stmt1.execute(createTableSql);
                            System.out.println("从mysql迁移到mysql的表：" + tableName + "表结构迁移成功！");
                        } catch (SQLException e) {
                            e.printStackTrace();
                            System.out.println("从mysql迁移到mysql的表：" + tableName + "表结构迁移失败！" + e.getMessage());
                        }
                }
            }
            //迁移索引
            if(synType.contains("6")) {
                //因为迁移表的时候已经把索引迁移了，所以这里不需要迁移索引
            }
            if (synType.contains("2")) {
                transData(threadDatas, (String) param.get("fromSchema"),(String) param.get("toSchema"),(String) param.get("fromType"),sourceConn,toConn);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            sourceConn.close();
            toConn.close();
        }
    }

    /**
     * mysql --> postgresql
     * @param threadDatas 迁移的表或者sql语句
     * @param param 页面写入的参数
     */
    private void excuteFromMysqlToPostgresql(List<String> threadDatas, Map<String, Object> param) throws SQLException {
        Connection sourceConn = getSourceConn(param);
        Connection toConn = getToConn(param);
        try {
            List<String> synType = (List) param.get("synType");
            //迁移表结构
            if (synType.contains("1")) {
                for (String tableName : threadDatas) {
                    //获取表名,如果输入的是sql语句，则取from之后的为表名
                    tableName = getTableName(tableName);
                    //先判断目标数据库是否存在表，如有删除表
                    dropTableIfExist(tableName,param,toConn,0);
                    System.out.println("当前迁移的表为：" + tableName);
                    //从源数据库中获取表结构
                    ResultSet columns;
                    try (Statement stmt = sourceConn.createStatement()) {
                        String selectMysqlTable = "select COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION, NUMERIC_SCALE,is_nullable, column_default,COLUMN_COMMENT " +
                                "from information_schema.`COLUMNS` where  TABLE_SCHEMA = '" + getToSelectTableName((String) param.get("fromSchema")).replace(".","")
                                +  "' and TABLE_NAME = '" + tableName.toLowerCase(Locale.ROOT) + "'";
                        columns = stmt.executeQuery(selectMysqlTable);
                        StringBuilder createTableSql1 = new StringBuilder("CREATE TABLE ").append(param.get("toSchema").toString()).append(".").append(tableName).append(" (");
                        String comments = "COMMENT ON COLUMN  " + tableName + ".";
                        StringBuilder commentsSql = new StringBuilder();
                        while (columns.next()) {
                            //列的名称。
                            String columnName = columns.getString(1);
                            //这是在迁移过程中遇到的问题，列名中有\n，所以需要替换掉
                            columnName = columnName.replace("\n","");
                            //列的数据类型的数据库特定名称，如VARCHAR2, INT, TEXT等。
                            String fromColumnType = columns.getString(2);
                            //列的最大字节数。对于数值类型，这可能是最大精度。
                            Long fromDataSize = columns.getLong(3);
                            //对于数值类型，表示小数点右侧的数字位数；对于其他类型，通常为null或0。
                            int fromDecimalDigits = columns.getInt(4);
                            //如果是数值类型，表示基数（通常是10或2）；对于非数值类型，返回null
                            int fromRadix = columns.getInt(5);
                            //列是否允许为空
                            String nullable = columns.getString(6).equals("YES") ? "" : " NOT NULL";
                            //列的默认值
                            String defaultValue = columns.getString(7);
                            if(defaultValue != null && !"".equals(defaultValue) && !"NULL".equals(defaultValue)) {
                                defaultValue = defaultValue.replace("'", "''");
                                defaultValue = "'" + defaultValue + "'";
                            }else if("NULL".equals(defaultValue)){
                                defaultValue = null;
                            }else{
                                defaultValue = null;
                            }
                            //列的注释或描述。
                            String remarks = columns.getString(8);
                            String toColumnType = "";
                            if("PostgreSQL".equals(param.get("toType")) || "VastBase".equals(param.get("toType"))) {
                                //pg VastBase
                                toColumnType = translateColumnTypeFromMysqlToPostgresql(fromColumnType, fromDataSize, fromDecimalDigits, fromRadix, nullable, defaultValue);
                            }

                            createTableSql1.append(columnName.toLowerCase(Locale.ROOT)).append(" ").append(toColumnType);

                            createTableSql1.append(", ");
                            if(remarks != null && !"".equals(remarks) ) {
                                commentsSql.append(comments).append(columnName.toLowerCase(Locale.ROOT)).append(" IS '" + remarks + "';");
                            }
                        }
                        //表的注释或描述。
                        String table_comments = "";
                        try (Statement stmt_source = sourceConn.createStatement()) {
                            String selectTableComments = "SELECT table_comment FROM information_schema.tables WHERE TABLE_NAME = '" + tableName.toUpperCase(Locale.ROOT) +
                                    "' and TABLE_SCHEMA = '" + getToSelectTableName((String) param.get("fromSchema")).replace(".","") + "'";
                            ResultSet rs = stmt_source.executeQuery(selectTableComments);
                            if (rs.next()) {
                                table_comments = rs.getString(1);
                                commentsSql.append("COMMENT ON TABLE ").append(param.get("toSchema").toString()).append(".").append(tableName).append(" IS '" + table_comments + "';");
                            }
                        } catch (SQLException e) {
                            System.out.println("获取表注释失败");
                        }

                        // Remove trailing comma and space
                        if (createTableSql1.length() > 0) {
                            createTableSql1.setLength(createTableSql1.length() - 2);
                        }
                        createTableSql1.append(") ;");
                        try (Statement stmt1 = toConn.createStatement()) {
                            stmt1.execute(createTableSql1.toString());
                            stmt1.execute(commentsSql.toString());
                            System.out.println("从Mysql迁移到postgres的表：" + tableName + "表结构迁移成功！");
                        } catch (SQLException e) {
                            e.printStackTrace();
                            System.out.println("从Mysql迁移到postgres的表：" + tableName + "表结构迁移失败！" + e.getMessage());
                        }
                    } catch (SQLException e) {
                        System.out.println("获取列失败" + e.getMessage());
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
            //迁移索引
            if(synType.contains("6")){
                for (String tableName : threadDatas) {
                    //获取表名,如果输入的是sql语句，则取from之后的为表名
                    tableName = getTableName(tableName);
                    //先判断目标数据库是否存在该索引，如有删除表
                    DatabaseMetaData toMetaData1 = toConn.getMetaData();
                    ResultSet toIndexs = toMetaData1.getIndexInfo(param.get("toDbName").toString(), null, tableName, false, true);

                    while (toIndexs.next()) {
                        String dropIndexSql = " DROP INDEX " + param.get("toSchema") + "." +toIndexs.getString("INDEX_NAME");
                        System.out.println(dropIndexSql);
                        try (PreparedStatement dropIndexStatement = toConn.prepareStatement(dropIndexSql)) {
                            dropIndexStatement.execute();
                        } catch (SQLException e) {
                            // Handle exception gracefully (e.g. log message and move on)
                            System.err.println("删除" +  tableName + "." + toIndexs.getString("INDEX_NAME") + "失败： " + e.getMessage());
                        }
                    }
                    // 查询指定表的索引
                    ResultSet indexes;
                    try (Statement stmt = sourceConn.createStatement()) {
                        String sql = "SELECT index_name,group_concat(COLUMN_name) COLUMN_name FROM information_schema.statistics \n" +
                                "WHERE TABLE_SCHEMA = '" + getToSelectTableName((String) param.get("fromSchema")).replace(".","") + "'" +
                                " AND  table_name = '" + tableName.toUpperCase(Locale.ROOT) + "'" +
                                "group by INDEX_name\n" ;
                        indexes = stmt.executeQuery(sql);
                        while (indexes.next()) {
                            String indexName = indexes.getString(1);
                            String columnName = indexes.getString(2);
                            // 转换为MySQL的索引创建语句
                            String mysqlIndexSql = "";
                            if (!indexName.contains("PRIMARY") && !indexName.contains("UNIQUE")) {
                                // MySQL的语法中，索引是否唯一不需要特别处理，因为默认就是非唯一的，如果需要唯一索引，则需调整此处逻辑
                                // 这里保持原逻辑，因为Oracle的NON_UNIQUE=true表示非唯一索引，与MySQL默认行为一致
                                mysqlIndexSql = String.format("CREATE INDEX %s ON %s.%s USING btree (%s);",
                                        indexName.trim().toLowerCase(),
                                        param.get("toSchema").toString(),
                                        tableName.toLowerCase(),
                                        columnName.toLowerCase());
                            }else{
                                indexName = indexName.replace("PRIMARY","").replace("UNIQUE","").replace(",","");
                                mysqlIndexSql = String.format("CREATE UNIQUE INDEX %s ON %s.%s USING btree (%s) ;",
                                        indexName.trim().toLowerCase(),
                                        param.get("toSchema").toString(),
                                        tableName.toLowerCase(),
                                        columnName.toLowerCase());
                            }
                            // 在MySQL中执行创建索引的SQL
                            try (Statement mysqlStmt = toConn.createStatement()) {
                                System.out.println(mysqlIndexSql);
                                mysqlStmt.executeUpdate(mysqlIndexSql);
                                System.out.println(tableName + "表的索引"+ indexName + "迁移成功！");
                            } catch (SQLException e) {
                                System.err.println(tableName + "表的索引迁移失败！" + indexName + "' in MySQL: " + e.getMessage());
                            }
                        }
                    }
                }
            }
            //迁移数据
            if(synType.contains("2")){
                transData(threadDatas, (String) param.get("fromSchema"),(String) param.get("toSchema"), (String) param.get("fromType"),sourceConn,toConn);
            }
            //迁移视图
            if(synType.contains("3")){
                for (String tableName : threadDatas) {
                    //获取表名,如果输入的是sql语句，则取from之后的为表名
                    tableName = getTableName(tableName);
                    //先判断目标数据库是否存在视图，如有删除视图
                    dropTableIfExist(tableName, param, toConn,1);
                    System.out.println("当前迁移的视图为：" + tableName + "开始迁移时间：" + LocalDateTime.now());
                    //从源数据库中获取表结构
                    ResultSet columns;
                    try (Statement stmt = sourceConn.createStatement()) {
                        columns = stmt.executeQuery("SELECT table_name,view_definition\n" +
                                "FROM information_schema.VIEWS\n" +
                                "WHERE TABLE_SCHEMA = '" + getFromSelectTableName((String) param.get("fromSchema")).replace(".","") + "'" +
                                "and table_name = '" + tableName.toUpperCase(Locale.ROOT) + "'");
                        StringBuilder createTableSql1 = new StringBuilder("CREATE view   ");

                        while (columns.next()) {
                            //视图名称。
                            String viewName = columns.getString(1);
                            //视图的定义。
                            String viewDefinition= columns.getString(2);
                            viewDefinition = viewDefinition.replace("`","").replace(getFromSelectTableName((String) param.get("fromSchema")),"");
                            //针对视图中用到!() 进行的单独处理。v_company_part
                            viewDefinition = viewDefinition.replace("!(","not (").replace("! (","not (");
                            //这里应该还有一个语法兼容问题，遇到在处理
                            createTableSql1.append(viewName).append(" as ").append(viewDefinition).append(";");
                        }

                        try (Statement stmt1 = toConn.createStatement()) {
                            stmt1.execute(createTableSql1.toString());
                            System.out.println("从mysql迁移到postgres的视图：" + tableName + "迁移成功！视图迁移结束时间：" + LocalDateTime.now() );
                        } catch (SQLException e) {
                            e.printStackTrace();
                            System.out.println("从mysql迁移到postgres的视图：" + tableName + "迁移失败！有语法兼容问题，请自行修改。" + e.getMessage());
                        }
                    } catch (SQLException e) {
                        System.out.println("获取视图定义失败或未查询到该视图" + e.getMessage());
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }

            if(synType.contains("7")){
                for (String tableName : threadDatas) {
                    //获取表名,如果输入的是sql语句，则取from之后的为表名
                    tableName = getTableName(tableName);
                    //先判断目标数据库是否存在约束，如有删除约束CONSTRAINT
//                    dropConstraintIfExist(tableName, param, toConn,1);
                    System.out.println("当前迁移的视图为：" + tableName + "开始迁移时间：" + LocalDateTime.now());
                    //从源数据库中获取表结构
                    ResultSet columns;
                    try (Statement stmt = sourceConn.createStatement()) {
                        columns = stmt.executeQuery("SELECT table_name,view_definition\n" +
                                "FROM information_schema.VIEWS\n" +
                                "WHERE TABLE_SCHEMA = '" + getFromSelectTableName((String) param.get("fromSchema")).replace(".","") + "'" +
                                "and table_name = '" + tableName.toUpperCase(Locale.ROOT) + "'");
                        StringBuilder createTableSql1 = new StringBuilder("CREATE view   ");

                        while (columns.next()) {
                            //视图名称。
                            String viewName = columns.getString(1);
                            //视图的定义。
                            String viewDefinition= columns.getString(2);
                            viewDefinition = viewDefinition.replace("`","").replace(getFromSelectTableName((String) param.get("fromSchema")),"");
                            //针对视图中用到!() 进行的单独处理。v_company_part
                            viewDefinition = viewDefinition.replace("!(","not (").replace("! (","not (");
                            //这里应该还有一个语法兼容问题，遇到在处理
                            createTableSql1.append(viewName).append(" as ").append(viewDefinition).append(";");
                        }

                        try (Statement stmt1 = toConn.createStatement()) {
                            stmt1.execute(createTableSql1.toString());
                            System.out.println("从mysql迁移到postgres的视图：" + tableName + "迁移成功！视图迁移结束时间：" + LocalDateTime.now() );
                        } catch (SQLException e) {
                            e.printStackTrace();
                            System.out.println("从mysql迁移到postgres的视图：" + tableName + "迁移失败！有语法兼容问题，请自行修改。" + e.getMessage());
                        }
                    } catch (SQLException e) {
                        System.out.println("获取视图定义失败或未查询到该视图" + e.getMessage());
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }

        }catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if (sourceConn != null) {
                    sourceConn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (toConn != null) {
                    toConn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * oracle --> mysql
     * @param threadDatas 迁移的表或者sql语句
     * @param param 页面写入的参数
     * @throws SQLException
     */
    public  void excuteFromOracleToMysql(List<String> threadDatas,Map<String, Object> param) throws SQLException {
        Connection sourceConn = getSourceConn(param);
        Connection toConn = getToConn(param);
        try {
            List<String> synType = (List) param.get("synType");
            //迁移表结构
            if (synType.contains("1")) {
                for (String tableName : threadDatas) {
                    //获取表名,如果输入的是sql语句，则取from之后的为表名
                    tableName = getTableName(tableName);
                    //先判断目标数据库是否存在表，如有删除表
                    dropTableIfExist(tableName,param,toConn,0);
                    System.out.println("当前迁移的表为：" + tableName);
                    //从源数据库中获取表结构
                    ResultSet columns;
                    try (Statement stmt = sourceConn.createStatement()) {
                        columns = stmt.executeQuery("SELECT  column_name,data_type,data_length,data_precision,data_scale,nullable,data_default FROM user_tab_columns WHERE TABLE_NAME = '" + tableName.toUpperCase(Locale.ROOT) + "'");
                        StringBuilder createTableSql1 = new StringBuilder("CREATE TABLE  ").append(tableName).append(" (");

                        while (columns.next()) {
                            //列的名称。
                            String columnName = columns.getString(1);
                            //列的数据类型的数据库特定名称，如VARCHAR2, INT, TEXT等。
                            String fromColumnType = columns.getString(2);
                            //列的最大字节数。对于数值类型，这可能是最大精度。
                            int fromDataSize = columns.getInt(3);
                            //对于数值类型，表示小数点右侧的数字位数；对于其他类型，通常为null或0。
                            int fromDecimalDigits = columns.getInt(4);
                            //如果是数值类型，表示基数（通常是10或2）；对于非数值类型，返回null
                            int fromRadix = columns.getInt(5);
                            //列是否允许为空
                            String nullable = columns.getString(6).equals("Y") ? "" : " NOT NULL";
                            //列的默认值
                            String defaultValue = columns.getString(7);
                            //列的注释或描述。 之前用的columns.getString("REMARKS")总是返回null
                            String remarks = "";
                            try (Statement stmt2 = sourceConn.createStatement()) {
                                ResultSet rs = stmt2.executeQuery("SELECT COMMENTS FROM user_col_comments WHERE TABLE_NAME = '" + tableName.toUpperCase(Locale.ROOT) + "' AND COLUMN_NAME = '" + columnName.toUpperCase(Locale.ROOT) + "'");
                                if (rs.next()) {
                                    remarks = rs.getString(1);
                                }
                            } catch (SQLException e) {
                                System.out.println("获取列注释失败");
                            }
                            String toColumnType = translateColumnTypeFromOracleToMysql(fromColumnType, fromDataSize, fromDecimalDigits, fromRadix, remarks, nullable, defaultValue);

                            createTableSql1.append(columnName.toLowerCase(Locale.ROOT)).append(" ").append(toColumnType);

                            createTableSql1.append(", ");
                        }
                        //表的注释或描述。
                        String table_comments = "";
                        try (Statement stmt_source = sourceConn.createStatement()) {
                            ResultSet rs = stmt_source.executeQuery("SELECT COMMENTS FROM user_tab_comments WHERE TABLE_NAME = '" + tableName.toUpperCase(Locale.ROOT) + "'");

                            if (rs.next()) {
                                table_comments = rs.getString(1);
                            }
                        } catch (SQLException e) {
                            System.out.println("获取表注释失败");
                        }

                        // Remove trailing comma and space
                        if (createTableSql1.length() > 0) {
                            createTableSql1.setLength(createTableSql1.length() - 2);
                        }

                        createTableSql1.append(") COMMENT = '").append(table_comments).append("';");

                        try (Statement stmt1 = toConn.createStatement()) {
                            stmt1.execute(createTableSql1.toString());
                            System.out.println("从oracle迁移到mysql的表：" + tableName + "表结构迁移成功！");
                        } catch (SQLException e) {
                            e.printStackTrace();
                            System.out.println("从oracle迁移到mysql的表：" + tableName + "表结构迁移失败！" + e.getMessage());
                        }
                    } catch (SQLException e) {
                        System.out.println("获取列失败" + e.getMessage());
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
            //迁移索引
            if(synType.contains("6")){
                for (String tableName : threadDatas) {
                    //获取表名,如果输入的是sql语句，则取from之后的为表名
                    tableName = getTableName(tableName);
                    //先判断目标数据库是否存在该索引，如有删除表
                    DatabaseMetaData toMetaData1 = toConn.getMetaData();
                    ResultSet toIndexs = toMetaData1.getIndexInfo(param.get("toDbName").toString(), null, tableName, false, true);
                    while (toIndexs.next()) {
                        String dropIndexSql = "ALTER TABLE " + tableName +  " DROP INDEX " + toIndexs.getString("INDEX_NAME");
                        System.out.println(dropIndexSql);
                        try (PreparedStatement dropIndexStatement = toConn.prepareStatement(dropIndexSql)) {
                            dropIndexStatement.execute();
                        } catch (SQLException e) {
                            // Handle exception gracefully (e.g. log message and move on)
                            System.err.println("删除" +  tableName + "." + toIndexs.getString("INDEX_NAME") + "失败： " + e.getMessage());
                        }
                    }
                    //获取目标数据库的索引，写入目标数据库
                    DatabaseMetaData metaData = sourceConn.getMetaData();
                    // 查询指定表的索引
                    ResultSet indexes;
                    try (Statement stmt = sourceConn.createStatement()) {
                        String sql = " select INDEX_NAME,\n" +
                                "           UNIQUENESS,\n" +
                                "           (select listagg(COLUMN_NAME, ', ') within group(order by index_name)\n" +
                                "              from ALL_IND_COLUMNS b\n" +
                                "             where b.INDEX_NAME = t.INDEX_NAME) COLUNM_NAME\n" +
                                "      from ALL_INDEXES t\n" +
                                "     where TABLE_NAME = '"+ tableName.toUpperCase(Locale.ROOT) + "'";
                        indexes = stmt.executeQuery(sql);
                        while (indexes.next()) {
                            String indexName = indexes.getString(1);
                            String columnName = indexes.getString(3);
                            boolean nonUnique = "UNIQUE".equals(indexes.getString(2)) ? false : true;

                            // 转换为MySQL的索引创建语句
                            String mysqlIndexSql = "";
                            if (nonUnique) {
                                // MySQL的语法中，索引是否唯一不需要特别处理，因为默认就是非唯一的，如果需要唯一索引，则需调整此处逻辑
                                // 这里保持原逻辑，因为Oracle的NON_UNIQUE=true表示非唯一索引，与MySQL默认行为一致
                                mysqlIndexSql = String.format("ALTER TABLE %s ADD  INDEX %s (%s)  USING BTREE;",
                                        tableName.toLowerCase(),
                                        indexName.toLowerCase(),
                                        columnName.toLowerCase());
                            }else{
                                mysqlIndexSql = String.format("ALTER TABLE %s ADD UNIQUE INDEX %s (%s)  USING BTREE;",
                                        tableName.toLowerCase(),
                                        indexName.toLowerCase(),
                                        columnName.toLowerCase());
                            }
                            // 在MySQL中执行创建索引的SQL
                            try (Statement mysqlStmt = toConn.createStatement()) {
                                mysqlStmt.executeUpdate(mysqlIndexSql);
                                System.out.println(tableName + "表的索引"+ indexName + "迁移成功！");
                            } catch (SQLException e) {
                                System.err.println(tableName + "表的索引迁移失败！" + indexName + "' in MySQL: " + e.getMessage());
                            }
                          }
                    }
                }
            }
            //迁移数据,这一段可以抽取成公共函数
            if(synType.contains("2")){
                transData(threadDatas, (String) param.get("fromSchema"),(String) param.get("toSchema"),(String) param.get("fromType"),sourceConn,toConn);
            }

        }catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if (sourceConn != null) {
                    sourceConn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (toConn != null) {
                    toConn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * oracle --> oracle
     * @param threadDatas 迁移的表或者sql语句
     * @param param 页面写入的参数
     * @throws SQLException
     */
    public  void excuteFromOracleToOracle(List<String> threadDatas,Map<String, Object> param) throws SQLException {
        Connection sourceConn = getSourceConn(param);
        Connection toConn = getToConn(param);
        try {
            List<String> synType = (List) param.get("synType");
            //迁移表结构
//            if (synType.contains("1")) {
//                for (String tableName : threadDatas) {
//                    //获取表名,如果输入的是sql语句，则取from之后的为表名
//                    tableName = getTableName(tableName);
//                    //先判断目标数据库是否存在表，如有删除表
//                    dropTableIfExist(tableName,param,toConn,0);
//                    System.out.println("当前迁移的表为：" + tableName);
//                    //从源数据库中获取表结构
//                    ResultSet columns;
//                    try (Statement stmt = sourceConn.createStatement()) {
//                        columns = stmt.executeQuery("SELECT  column_name,data_type,data_length,data_precision,data_scale,nullable,data_default FROM user_tab_columns WHERE TABLE_NAME = '" + tableName.toUpperCase(Locale.ROOT) + "'");
//                        StringBuilder createTableSql1 = new StringBuilder("CREATE TABLE  ").append(tableName).append(" (");
//
//                        while (columns.next()) {
//                            //列的名称。
//                            String columnName = columns.getString(1);
//                            //列的数据类型的数据库特定名称，如VARCHAR2, INT, TEXT等。
//                            String fromColumnType = columns.getString(2);
//                            //列的最大字节数。对于数值类型，这可能是最大精度。
//                            int fromDataSize = columns.getInt(3);
//                            //对于数值类型，表示小数点右侧的数字位数；对于其他类型，通常为null或0。
//                            int fromDecimalDigits = columns.getInt(4);
//                            //如果是数值类型，表示基数（通常是10或2）；对于非数值类型，返回null
//                            int fromRadix = columns.getInt(5);
//                            //列是否允许为空
//                            String nullable = columns.getString(6).equals("Y") ? "" : " NOT NULL";
//                            //列的默认值
//                            String defaultValue = columns.getString(7);
//                            //列的注释或描述。 之前用的columns.getString("REMARKS")总是返回null
//                            String remarks = "";
//                            try (Statement stmt2 = sourceConn.createStatement()) {
//                                ResultSet rs = stmt2.executeQuery("SELECT COMMENTS FROM user_col_comments WHERE TABLE_NAME = '" + tableName.toUpperCase(Locale.ROOT) + "' AND COLUMN_NAME = '" + columnName.toUpperCase(Locale.ROOT) + "'");
//                                if (rs.next()) {
//                                    remarks = rs.getString(1);
//                                }
//                            } catch (SQLException e) {
//                                System.out.println("获取列注释失败");
//                            }
//                            String toColumnType = translateColumnTypeFromOracleToMysql(fromColumnType, fromDataSize, fromDecimalDigits, fromRadix, remarks, nullable, defaultValue);
//
//                            createTableSql1.append(columnName.toLowerCase(Locale.ROOT)).append(" ").append(toColumnType);
//
//                            createTableSql1.append(", ");
//                        }
//                        //表的注释或描述。
//                        String table_comments = "";
//                        try (Statement stmt_source = sourceConn.createStatement()) {
//                            ResultSet rs = stmt_source.executeQuery("SELECT COMMENTS FROM user_tab_comments WHERE TABLE_NAME = '" + tableName.toUpperCase(Locale.ROOT) + "'");
//
//                            if (rs.next()) {
//                                table_comments = rs.getString(1);
//                            }
//                        } catch (SQLException e) {
//                            System.out.println("获取表注释失败");
//                        }
//
//                        // Remove trailing comma and space
//                        if (createTableSql1.length() > 0) {
//                            createTableSql1.setLength(createTableSql1.length() - 2);
//                        }
//
//                        createTableSql1.append(") COMMENT = '").append(table_comments).append("';");
//
//                        try (Statement stmt1 = toConn.createStatement()) {
//                            stmt1.execute(createTableSql1.toString());
//                            System.out.println("从oracle迁移到mysql的表：" + tableName + "表结构迁移成功！");
//                        } catch (SQLException e) {
//                            e.printStackTrace();
//                            System.out.println("从oracle迁移到mysql的表：" + tableName + "表结构迁移失败！" + e.getMessage());
//                        }
//                    } catch (SQLException e) {
//                        System.out.println("获取列失败" + e.getMessage());
//                    } catch (Exception e) {
//                        e.printStackTrace();
//                    }
//                }
//            }
            //迁移索引
//            if(synType.contains("6")){
//                for (String tableName : threadDatas) {
//                    //获取表名,如果输入的是sql语句，则取from之后的为表名
//                    tableName = getTableName(tableName);
//                    //先判断目标数据库是否存在该索引，如有删除表
//                    DatabaseMetaData toMetaData1 = toConn.getMetaData();
//                    ResultSet toIndexs = toMetaData1.getIndexInfo(param.get("toDbName").toString(), null, tableName, false, true);
//                    while (toIndexs.next()) {
//                        String dropIndexSql = "ALTER TABLE " + tableName +  " DROP INDEX " + toIndexs.getString("INDEX_NAME");
//                        System.out.println(dropIndexSql);
//                        try (PreparedStatement dropIndexStatement = toConn.prepareStatement(dropIndexSql)) {
//                            dropIndexStatement.execute();
//                        } catch (SQLException e) {
//                            // Handle exception gracefully (e.g. log message and move on)
//                            System.err.println("删除" +  tableName + "." + toIndexs.getString("INDEX_NAME") + "失败： " + e.getMessage());
//                        }
//                    }
//                    //获取目标数据库的索引，写入目标数据库
//                    DatabaseMetaData metaData = sourceConn.getMetaData();
//                    // 查询指定表的索引
//                    ResultSet indexes;
//                    try (Statement stmt = sourceConn.createStatement()) {
//                        String sql = " select INDEX_NAME,\n" +
//                                "           UNIQUENESS,\n" +
//                                "           (select listagg(COLUMN_NAME, ', ') within group(order by index_name)\n" +
//                                "              from ALL_IND_COLUMNS b\n" +
//                                "             where b.INDEX_NAME = t.INDEX_NAME) COLUNM_NAME\n" +
//                                "      from ALL_INDEXES t\n" +
//                                "     where TABLE_NAME = '"+ tableName.toUpperCase(Locale.ROOT) + "'";
//                        indexes = stmt.executeQuery(sql);
//                        while (indexes.next()) {
//                            String indexName = indexes.getString(1);
//                            String columnName = indexes.getString(3);
//                            boolean nonUnique = "UNIQUE".equals(indexes.getString(2)) ? false : true;
//
//                            // 转换为MySQL的索引创建语句
//                            String mysqlIndexSql = "";
//                            if (nonUnique) {
//                                // MySQL的语法中，索引是否唯一不需要特别处理，因为默认就是非唯一的，如果需要唯一索引，则需调整此处逻辑
//                                // 这里保持原逻辑，因为Oracle的NON_UNIQUE=true表示非唯一索引，与MySQL默认行为一致
//                                mysqlIndexSql = String.format("ALTER TABLE %s ADD  INDEX %s (%s)  USING BTREE;",
//                                        tableName.toLowerCase(),
//                                        indexName.toLowerCase(),
//                                        columnName.toLowerCase());
//                            }else{
//                                mysqlIndexSql = String.format("ALTER TABLE %s ADD UNIQUE INDEX %s (%s)  USING BTREE;",
//                                        tableName.toLowerCase(),
//                                        indexName.toLowerCase(),
//                                        columnName.toLowerCase());
//                            }
//                            // 在MySQL中执行创建索引的SQL
//                            try (Statement mysqlStmt = toConn.createStatement()) {
//                                mysqlStmt.executeUpdate(mysqlIndexSql);
//                                System.out.println(tableName + "表的索引"+ indexName + "迁移成功！");
//                            } catch (SQLException e) {
//                                System.err.println(tableName + "表的索引迁移失败！" + indexName + "' in MySQL: " + e.getMessage());
//                            }
//                        }
//                    }
//                }
//            }
            //迁移数据,这一段可以抽取成公共函数
            if(synType.contains("2")){
                transData(threadDatas, (String) param.get("fromSchema"),(String) param.get("toSchema"),(String) param.get("fromType"),sourceConn,toConn);
            }

        }catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if (sourceConn != null) {
                    sourceConn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (toConn != null) {
                    toConn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * oracle --> postgresql
     * @param threadDatas 迁移的表或者sql语句
     * @param param 页面写入的参数
     * @throws SQLException
     */
    public  void excuteFromOracleToPostgresql(List<String> threadDatas,Map<String, Object> param) throws SQLException {
        Connection sourceConn = getSourceConn(param);
        Connection toConn = getToConn(param);
        try {
            List<String> synType = (List) param.get("synType");
            //迁移表结构
            if (synType.contains("1")) {
                for (String tableName : threadDatas) {
                    //获取表名,如果输入的是sql语句，则取from之后的为表名
                    tableName = getTableName(tableName);
                    //先判断目标数据库是否存在表，如有删除表
                    dropTableIfExist(tableName,param,toConn,0);
                    System.out.println("当前迁移的表为：" + tableName);
                    //从源数据库中获取表结构
                    ResultSet columns;
                    try (Statement stmt = sourceConn.createStatement()) {
                        columns = stmt.executeQuery("SELECT  column_name,data_type,data_length,data_precision,data_scale,nullable,data_default FROM user_tab_columns WHERE TABLE_NAME = '" + tableName.toUpperCase(Locale.ROOT) + "'");
                        StringBuilder createTableSql1 = new StringBuilder("CREATE TABLE  ").append(param.get("toSchema").toString()).append(".").append(tableName).append(" (");
                        String comments = "COMMENT ON COLUMN " + tableName + ".";
                        StringBuilder commentsSql = new StringBuilder();
                        while (columns.next()) {
                            //列的名称。
                            String columnName = columns.getString(1);
                            //列的数据类型的数据库特定名称，如VARCHAR2, INT, TEXT等。
                            String fromColumnType = columns.getString(2);
                            //列的最大字节数。对于数值类型，这可能是最大精度。
                            int fromDataSize = columns.getInt(3);
                            //对于数值类型，表示小数点右侧的数字位数；对于其他类型，通常为null或0。
                            int fromDecimalDigits = columns.getInt(4);
                            //如果是数值类型，表示基数（通常是10或2）；对于非数值类型，返回null
                            int fromRadix = columns.getInt(5);
                            //列是否允许为空
                            String nullable = columns.getString(6).equals("Y") ? "" : " NOT NULL";
                            //列的默认值
                            String defaultValue = columns.getString(7);
                            //列的注释或描述。 之前用的columns.getString("REMARKS")总是返回null
                            String remarks = "";
                            try (Statement stmt2 = sourceConn.createStatement()) {
                                ResultSet rs = stmt2.executeQuery("SELECT COMMENTS FROM user_col_comments WHERE TABLE_NAME = '" + tableName.toUpperCase(Locale.ROOT) + "' AND COLUMN_NAME = '" + columnName.toUpperCase(Locale.ROOT) + "'");
                                if (rs.next()) {
                                    remarks = rs.getString(1);
                                    if(remarks != null && remarks.contains("'")){
                                        remarks = remarks.replaceAll("'","''");
                                    }
                                }
                            } catch (SQLException e) {
                                System.out.println("获取列注释失败");
                            }
                            String toColumnType = "";
                            if("PostgreSQL".equals(param.get("toType")) || "VastBase".equals(param.get("toType"))) {
                                //pg VastBase
                                toColumnType = translateColumnTypeFromOracleToPostgresql(fromColumnType, fromDataSize, fromDecimalDigits, fromRadix, nullable, defaultValue,param.get("toType").toString());
                            }

                            createTableSql1.append(columnName.toLowerCase(Locale.ROOT)).append(" ").append(toColumnType);

                            createTableSql1.append(", ");
                            if(remarks != null && !"".equals(remarks) ) {
                                commentsSql.append(comments).append(columnName.toLowerCase(Locale.ROOT)).append(" IS '" + remarks + "';");
                            }
                        }
                        //表的注释或描述。
                        String table_comments = "";
                        try (Statement stmt_source = sourceConn.createStatement()) {
                            ResultSet rs = stmt_source.executeQuery("SELECT COMMENTS FROM user_tab_comments WHERE TABLE_NAME = '" + tableName.toUpperCase(Locale.ROOT) + "'");
                            if (rs.next()) {
                                table_comments = rs.getString(1);

                            }
                        } catch (SQLException e) {
                            System.out.println("获取表注释失败");
                        }

                        // Remove trailing comma and space
                        if (createTableSql1.length() > 0) {
                            createTableSql1.setLength(createTableSql1.length() - 2);
                        }

                        createTableSql1.append(") ;");
                        commentsSql.append("COMMENT ON TABLE ").append(param.get("toSchema").toString()).append(".").append(tableName).append(" IS '" + table_comments + "';");

                        try (Statement stmt1 = toConn.createStatement()) {
                            stmt1.execute(createTableSql1.toString());
                            stmt1.execute(commentsSql.toString());
                            System.out.println("从oracle迁移到postgres的表：" + tableName + "表结构迁移成功！");
                        } catch (SQLException e) {
                            e.printStackTrace();
                            System.out.println("从oracle迁移到postgres的表：" + tableName + "表结构迁移失败！" + e.getMessage());
                        }
                    } catch (SQLException e) {
                        System.out.println("获取列失败" + e.getMessage());
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
            //迁移索引
            if(synType.contains("6")){
                for (String tableName : threadDatas) {
                    //获取表名,如果输入的是sql语句，则取from之后的为表名
                    tableName = getTableName(tableName);
                    //先判断目标数据库是否存在该索引，如有删除表
                    DatabaseMetaData toMetaData1 = toConn.getMetaData();
                    ResultSet toIndexs = toMetaData1.getIndexInfo(param.get("toDbName").toString(), null, tableName, false, true);
                    while (toIndexs.next()) {
                        String dropIndexSql = " DROP INDEX " + param.get("toSchema") + "." +toIndexs.getString("INDEX_NAME");
//                        System.out.println(dropIndexSql);
//                        try (PreparedStatement dropIndexStatement = toConn.prepareStatement(dropIndexSql)) {
////                            dropIndexStatement.execute();
////                        } catch (SQLException e) {
////                            // Handle exception gracefully (e.g. log message and move on)
////                            System.err.println("删除" +  tableName + "." + toIndexs.getString("INDEX_NAME") + "失败： " + e.getMessage());
////                        }
                    }
                    //获取目标数据库的索引，写入目标数据库
                    DatabaseMetaData metaData = sourceConn.getMetaData();
                    // 查询指定表的索引
                    ResultSet indexes;
                    try (Statement stmt = sourceConn.createStatement()) {
//                        String sql = " select INDEX_NAME,\n" +
//                                "           UNIQUENESS,\n" +
//                                "           (select listagg(COLUMN_NAME, ', ') within group(order by index_name)\n" +
//                                "              from ALL_IND_COLUMNS b\n" +
//                                "             where b.INDEX_NAME = t.INDEX_NAME) COLUNM_NAME\n" +
//                                "      from ALL_INDEXES t\n" +
//                                "     where TABLE_NAME = '"+ tableName.toUpperCase(Locale.ROOT) + "'";
                        // oracle 11g之前的版本没有listagg 函数
                        String sql = " select INDEX_NAME,\n" +
                                "           UNIQUENESS,\n" +
                                "           (select wm_concat(COLUMN_NAME) " +
                                "              from ALL_IND_COLUMNS b\n" +
                                "             where b.INDEX_NAME = t.INDEX_NAME) COLUNM_NAME\n" +
                                "      from ALL_INDEXES t\n" +
                                "     where TABLE_NAME = '"+ tableName.toUpperCase(Locale.ROOT) + "'";
                        indexes = stmt.executeQuery(sql);
                        while (indexes.next()) {
                            String indexName = indexes.getString(1);
                            String columnName = indexes.getString(3);
                            boolean nonUnique = "UNIQUE".equals(indexes.getString(2)) ? false : true;

                            // 转换为MySQL的索引创建语句
                            String mysqlIndexSql = "";
                            if (nonUnique) {
                                // MySQL的语法中，索引是否唯一不需要特别处理，因为默认就是非唯一的，如果需要唯一索引，则需调整此处逻辑
                                // 这里保持原逻辑，因为Oracle的NON_UNIQUE=true表示非唯一索引，与MySQL默认行为一致
                                mysqlIndexSql = String.format("CREATE INDEX %s ON %s.%s USING btree (%s);",
                                        indexName.toLowerCase(),
                                        param.get("toSchema").toString(),
                                        tableName.toLowerCase(),
                                        columnName.toLowerCase());
                            }else{
                                mysqlIndexSql = String.format("CREATE UNIQUE INDEX %s ON %s.%s USING btree (%s) ;",
                                        indexName.toLowerCase(),
                                        param.get("toSchema").toString(),
                                        tableName.toLowerCase(),
                                        columnName.toLowerCase());
                            }
                            // 在MySQL中执行创建索引的SQL
                            try (Statement mysqlStmt = toConn.createStatement()) {
                                mysqlStmt.executeUpdate(mysqlIndexSql);
                                System.out.println(tableName + "表的索引"+ indexName + "迁移成功！");
                            } catch (SQLException e) {
                                System.err.println(tableName + "表的索引迁移失败！" + indexName + "' in Oracle: " + e.getMessage());
                            }
                        }
                    }
                }
            }
            //迁移约束
            if(synType.contains("7")){
                for (String tableName : threadDatas) {
                    //获取表名,如果输入的是sql语句，则取from之后的为表名
                    tableName = getTableName(tableName);
                    System.out.println("开始" + tableName + "约束迁移");
                    //先判断目标数据库是否存在该约束，如有则删除该约束
                    this.dropAllConstraints(toConn,tableName,param.get("toSchema").toString());

                    //执行从oracle读取约束写入postgres
                    //获取主键约束的创建脚本
                    StringBuilder pkSql = this.migratePrimaryKey(sourceConn,tableName,param.get("toSchema").toString());
                    //获取外键约束的创建脚本
                    StringBuilder fkSql = this.migrateForeignKeys(sourceConn,tableName,param.get("toSchema").toString());
                    if(fkSql != null) {
                        pkSql = pkSql.append(fkSql);
                    }
                    //获取唯一键
                    StringBuilder ukSql = this.migrateUniqueConstraints(sourceConn,tableName,param.get("toSchema").toString());
                    if(ukSql != null) {
                        pkSql = pkSql.append(ukSql);
                    }
                    if(pkSql != null){
                        try (PreparedStatement conStatement = toConn.prepareStatement(pkSql.toString())) {
                            conStatement.execute();
                            System.out.println("表： " + tableName + "的约束迁移成功！！ " );
                        } catch (SQLException e) {
                            // Handle exception gracefully (e.g. log message and move on)
                            System.err.println("表： " + tableName + "的约束迁移失败: " + e.getMessage());
                        }
                    }else{
                        System.out.println("表： " + tableName + "无约束需要迁移！！ " );
                    }
                }
            }
            //迁移数据
            if(synType.contains("2")){
                transData(threadDatas, (String) param.get("fromSchema"),(String) param.get("toSchema"), (String) param.get("fromType"),sourceConn,toConn);
            }

        }catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if (sourceConn != null) {
                    sourceConn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (toConn != null) {
                    toConn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    private void dropAllConstraints(Connection connection, String tableName,String toSchema) throws SQLException {
        // 查询表的所有约束
        String sql = "SELECT conname AS constraint_name " +
                "FROM pg_constraint " +
                "WHERE conrelid = ?::regclass";
        StringBuilder uniqueSql = new StringBuilder();
        try (PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setString(1, tableName);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                String constraintName = rs.getString(1);
                // 生成并打印删除约束的SQL语句
                uniqueSql = uniqueSql.append("ALTER TABLE  ").append(toSchema).append(".").append(tableName).append(" DROP CONSTRAINT ").append(constraintName).append(";");
            }
        }
        try(PreparedStatement stmt1 = connection.prepareStatement(uniqueSql.toString())){
            stmt1.executeUpdate();
        } catch (Exception e){
            System.out.println("删除表" + tableName + "索引失败！" + e.getMessage());
        }
    }
    private StringBuilder migrateUniqueConstraints(Connection oracleConnection, String tableName,String toSchema) throws SQLException {
        // 获取唯一约束信息
        String sql = "SELECT con.CONSTRAINT_NAME, wm_concat(col.COLUMN_NAME) " +
                "FROM all_constraints con " +
                "JOIN all_cons_columns col ON con.constraint_name = col.constraint_name " +
                "WHERE con.table_name = ? AND con.constraint_type = 'U' AND con.GENERATED != 'GENERATED NAME'" +
                " group by con.CONSTRAINT_NAME";
        StringBuilder uniqueSql = null;
        try (PreparedStatement stmt = oracleConnection.prepareStatement(sql)) {
            stmt.setString(1, tableName.toUpperCase());
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                String constraintName = rs.getString(1);
                String columnName = rs.getString(2);
                uniqueSql = new StringBuilder("ALTER TABLE ").append(toSchema).append(".").append(tableName).append(" ADD CONSTRAINT ")
                        .append(constraintName).append(" UNIQUE (").append(columnName).append(");");
            }
        }
        return uniqueSql;
    }

    private StringBuilder migrateForeignKeys(Connection oracleConnection, String tableName,String toSchema) throws SQLException {
        // 获取外键信息
        String sql = "SELECT con.CONSTRAINT_NAME, con.R_CONSTRAINT_NAME, col.COLUMN_NAME, refcol.COLUMN_NAME AS REF_COLUMN_NAME, refcon.TABLE_NAME AS REF_TABLE_NAME " +
                "FROM all_constraints con " +
                "JOIN all_cons_columns col ON con.constraint_name = col.constraint_name " +
                "JOIN all_constraints refcon ON con.r_constraint_name = refcon.constraint_name " +
                "JOIN all_cons_columns refcol ON refcon.constraint_name = refcol.constraint_name " +
                "WHERE con.table_name = ? AND con.constraint_type = 'R' AND con.GENERATED != 'GENERATED NAME'";
        StringBuilder fkSql = null;
        try (PreparedStatement stmt = oracleConnection.prepareStatement(sql)) {
            stmt.setString(1, tableName.toUpperCase());
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                String constraintName = rs.getString(1);
                String columnName = rs.getString(3);
                String refColumnName = rs.getString(4);
                String refTableName = rs.getString(5);
                fkSql = new StringBuilder("ALTER TABLE ").append(toSchema).append(".").append(tableName).append(" ADD CONSTRAINT ")
                        .append(constraintName).append("  FOREIGN KEY (").append(columnName).append(") REFERENCES ").append(toSchema).append(".")
                        .append(refTableName).append("(").append(refColumnName).append(");");
            }
        }
        return fkSql;
    }
    private StringBuilder migratePrimaryKey(Connection oracleConnection, String tableName,String toSchema) throws SQLException {
        // 获取主键信息
        String sql = "SELECT con.CONSTRAINT_NAME, wm_concat(col.COLUMN_NAME) COLUMN_NAME " +
                "FROM all_constraints con " +
                "JOIN all_cons_columns col ON con.constraint_name = col.constraint_name " +
                "WHERE con.table_name = ? AND con.constraint_type = 'P' AND  con.GENERATED != 'GENERATED NAME' " +
                "group by con.CONSTRAINT_NAME";
        StringBuilder pkSql = null;
        try (PreparedStatement stmt = oracleConnection.prepareStatement(sql)) {
            stmt.setString(1, tableName.toUpperCase());
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                pkSql = new StringBuilder("ALTER TABLE ").append(toSchema).append(".").append(tableName).append(" ADD CONSTRAINT ")
                        .append(rs.getString(1)).append(" PRIMARY KEY (");
               String colTemp = rs.getString(2);
                pkSql.append(colTemp).append(");");
            }
        }
        return pkSql;
    }

    private String getTableName(String tableName){
        tableName = tableName.toLowerCase(Locale.ROOT);
        //这里处理表名中带关键字的，pp_administration_reimbursement_from;
        if (tableName.contains(" from ") && tableName.contains("select")) {
            if (tableName.contains("where") ) {
                tableName = tableName.substring(tableName.indexOf("from") + 4, tableName.indexOf("where"));
            } else {
                tableName = tableName.substring(tableName.indexOf("from") + 4);
            }
        }
        return tableName.trim();
    }

    /**
     * 获取源数据库连接
     * @param param 参数
     * @return
     * @throws SQLException
     */
    private Connection getSourceConn(Map<String,Object> param) throws SQLException {
        return DriverManager.getConnection(param.get("fromHost").toString(), param.get("fromUsername").toString(), param.get("fromPassword").toString());
    }

    /**
     * 获取目标数据库连接
     * @param param 参数
     * @return
     * @throws SQLException
     */
    private Connection getToConn(Map<String,Object> param) throws SQLException {
        return DriverManager.getConnection(param.get("toHost").toString(), param.get("toUsername").toString(), param.get("toPassword").toString());
    }

    /**
     * 删除目标数据库中的表
     * @param tableName 表名
     * @param param 参数
     * @param toConn 目标数据库连接
     * @throws SQLException
     */
    private void dropTableIfExist(String tableName,Map<String,Object> param,Connection toConn,int type) throws SQLException {
        DatabaseMetaData toMetaData1 = toConn.getMetaData();
        ResultSet tables = toMetaData1.getTables(param.get("toDbName").toString(), getToSelectTableName((String) param.get("toSchema")).replace(".",""), tableName, null);
        String dropTableSql = "";
        if (tables.next()) {
            if(type == 1){
                dropTableSql = "DROP view " + getToSelectTableName((String) param.get("toSchema")) + tableName;
            }else {
                dropTableSql = "DROP TABLE " + getToSelectTableName((String) param.get("toSchema")) + tableName;
            }
            System.out.println(dropTableSql);
            try (PreparedStatement dropTableStatement = toConn.prepareStatement(dropTableSql)) {
                dropTableStatement.execute();
            } catch (SQLException e) {
                // Handle exception gracefully (e.g. log message and move on)
                System.err.println("Error dropping table****** " + tableName + ": " + e.getMessage());
            }
        }
    }

    private String getToSelectTableName(String  toSchema){
        return toSchema == "" || toSchema == null ? "" : (toSchema + ".") ;
    }

    private String getFromSelectTableName(String  fromSchema){
        return fromSchema == "" || fromSchema == null ? "" : (fromSchema + ".") ;
    }

    /**
     * 数据迁移，从源库中读取出来写入目标库
     * @param threadDatas 需要迁移的表或者sql语句
     * @param sourceConn 源数据库连接
     * @param toConn 目标数据库连接
     * @throws SQLException
     */
    private void transData(List<String> threadDatas,String fromSchema,String toSchema,String fromType,
                           Connection sourceConn,Connection toConn) throws SQLException {
        for (String tableName : threadDatas) {
            //获取表名,如果输入的是sql语句，则取from之后的为表名
            //先判断目标数据库表中是否有数据，如有删除表数据
            tableName = tableName.toLowerCase(Locale.ROOT);
            System.out.println("表：" + tableName + "的数据迁移中");
            long step1 = System.currentTimeMillis();
            //用作数据迁移后的数据量比对
            String checkSql = "";
            //查询条件
            String whereCondition = "";
            String sql = "";
            String tableName_ = getTableName(tableName);
            if(!tableName.contains(" from ") && !tableName.contains("select")){
                //直接输入的表名，清除目标库数据
                 sql = "truncate table " + getToSelectTableName(toSchema) + tableName_;
                checkSql = "select count(*) from "  + tableName_;
            }else{
                //sql语句的话直接删除sql语句查询结果
                if(!tableName.contains("where")){
                    sql = "truncate table " + getToSelectTableName(toSchema) + tableName_  + "CASCADE";
                    checkSql = "select count(*) from "  + tableName_;
                }else{
                    whereCondition = tableName.substring(tableName.indexOf("where"), tableName.length());
                    sql = "delete from " + getToSelectTableName(toSchema) + tableName_ + " " + whereCondition.toUpperCase(Locale.ROOT);
                    checkSql = "select count(*)  from "  + tableName_ + " " + whereCondition.toUpperCase(Locale.ROOT) ;
                }

            }
            try (PreparedStatement dropDataStatement = toConn.prepareStatement(sql)) {
                dropDataStatement.execute();
            } catch (SQLException e) {
                // Handle exception gracefully (e.g. log message and move on)
                System.err.println("清除目标库表数据失败： " + getTableName(tableName) + ": " + e.getMessage());
            }
            //从源数据库中查询数据并写入目标数据库中
            DatabaseMetaData sourceMeta = sourceConn.getMetaData();
            //getColumns的参数分别指：数据库名，模式名，表名，列名
            ResultSet columns;
            ResultSetMetaData columns_ = null;
            switch (fromType){
                case  "Oracle10":
                case "Oracle":
                    columns = sourceMeta.getColumns(null, null, tableName_.toUpperCase(Locale.ROOT), null);
                    //不用DatabaseMetaData.getColumns()是因为返回了重复列，这在oracle中发现了此问题，先修改oralce的，其他类型的数据库如果也发现此问题再统一修改
                    Statement st = sourceConn.createStatement();
                    ResultSet rset = st.executeQuery("SELECT * FROM " + tableName_);
                    columns_ = rset.getMetaData();
                    break;
                case "MySQL":
                    columns = sourceMeta.getColumns(getFromSelectTableName(fromSchema).replace(".",""), null, tableName_.toUpperCase(Locale.ROOT), null);
                    break;
                case "PostgreSQL":
                case "VastBase":
                    columns = sourceMeta.getColumns(null, fromSchema, tableName_, null);
                    break;
                    default:
                       throw new RuntimeException("不支持的数据库类型");
            }

            StringBuilder selectColumns = new StringBuilder();
            StringBuilder insertColumns = new StringBuilder();

            int columnCount = 0;

            if("Oracle".equals(fromType)){
                for (int i=1; i<= columns_.getColumnCount(); i++){
                    if (columnCount > 0) {
                        selectColumns.append(", ");
                        insertColumns.append(", ");
                    }
                    String columnName = columns_.getColumnLabel(i);
                    //写入表结构时去除了\n的特殊字段，写入数据时也要去除
                    columnName = columnName.replace("\n", "").trim();
                    selectColumns.append(columnName);
                    insertColumns.append("?");
                    columnCount++;
                }
            }else {
                while (columns.next()) {
                    if (columnCount > 0) {
                        selectColumns.append(", ");
                        insertColumns.append(", ");
                    }
                    String columnName = columns.getString("COLUMN_NAME");
                    //写入表结构时去除了\n的特殊字段，写入数据时也要去除
                    columnName = columnName.replace("\n", "").trim();
                    if (fromType.equals("PostgreSQL") || fromType.equals("VastBase")) {
//                    selectColumns.append("\"").append(columnName).append("\"");
                        selectColumns.append(columnName.trim());
                        String columType = columns.getString("TYPE_NAME").toLowerCase(Locale.ROOT);
                        if ("bit".equals(columType)) {
                            insertColumns.append("? :: bit");
                        } else {
                            insertColumns.append("?");
                        }
                    } else {
                        selectColumns.append(columnName);
                        insertColumns.append("?");
                    }
                    columnCount++;
                }
            }
            // 构建查询和插入语句
            String selectQuery = "SELECT * FROM " + getFromSelectTableName(fromSchema) + tableName_  + " " + whereCondition.toUpperCase(Locale.ROOT);
            String insertQuery = "INSERT INTO " + getToSelectTableName(toSchema) + tableName_ + " (" + selectColumns.toString().toLowerCase(Locale.ROOT) + ") VALUES (" + insertColumns.toString() + ")";


            //看下数据库是否开启自动提交事务，如果有自动提交就不需要再额外提交事务
            boolean autoCommit = toConn.getAutoCommit();
            if(autoCommit){
                toConn.setAutoCommit(false);
            }

            try (PreparedStatement sourceStmt = sourceConn.prepareStatement(selectQuery);
                 PreparedStatement toStmt = toConn.prepareStatement(insertQuery)) {

                ResultSet resultSet = sourceStmt.executeQuery();
                ResultSetMetaData rsmd = null;
                rsmd = resultSet.getMetaData();
                int count = 0;
                DatabaseMetaData metaData = toConn.getMetaData();
                String dbProductName = metaData.getDatabaseProductName();

                while (resultSet.next()) {
                    for (int i = 1; i <= columnCount; i++) {
                        //由于MySQL数据库中存在零日期（0000-00-00）值，而Java中的LocalDateTime不支持零日期值
                        if("MySQL".equals(fromType) && resultSet.getObject(i) != null) {
                            if(resultSet.getObject(i) instanceof java.sql.Timestamp) {
                                java.sql.Timestamp ts = (java.sql.Timestamp) resultSet.getObject(i);
                                if(ts.getTime() == 0L) {
                                    toStmt.setNull(i, Types.TIMESTAMP);
                                } else {
                                    toStmt.setTimestamp(i, ts);
                                }
                            } else if(resultSet.getObject(i) instanceof java.sql.Date) {
                                java.sql.Date d = (java.sql.Date) resultSet.getObject(i);
                                if(d.getTime() == 0L) {
                                    toStmt.setNull(i, Types.DATE);
                                } else {
                                    toStmt.setDate(i, d);
                                }
                            } else {
                                toStmt.setObject(i, resultSet.getObject(i));
                            }
                        } else if("PostgreSQL".equals(fromType) && resultSet.getObject(i) != null){
                            if(resultSet.getObject(i) instanceof Boolean) {
                                toStmt.setInt(i, resultSet.getInt(i));
                            }else{
                                toStmt.setObject(i, resultSet.getObject(i));
                            }
                        }else if("Oracle".equals(fromType) || "Oracle10".equals(fromType)){
                            //目标库和源库是oralce的才这样操作
                            if("Oracle".equalsIgnoreCase(dbProductName)){
                                // 检查每一列的数据类型是否为 BLOB
                                if (rsmd.getColumnType(i) == Types.BLOB) {
                                    // 如果需要进一步处理BLOB数据，可以这样做
                                    Blob blob = resultSet.getBlob(i);
                                    // 处理BLOB
                                    if (blob != null) {
                                        try (InputStream inputStream = blob.getBinaryStream()) {
                                            toStmt.setBinaryStream(i, inputStream, (int) blob.length());
                                        }
                                    } else {
                                        toStmt.setNull(i, Types.BLOB);
                                    }
                                }else if(rsmd.getColumnType(i) == Types.CLOB){
                                    Clob clob = resultSet.getClob(i);
                                    // 处理CLOB
                                    if (clob != null) {
                                        try (Reader reader = clob.getCharacterStream()) {
                                            toStmt.setCharacterStream(i, reader, (int) clob.length());
                                        }
                                    } else {
                                        toStmt.setNull(i, Types.CLOB);
                                    }
                                }else{
                                    toStmt.setObject(i, resultSet.getObject(i));
                                }
                            }
                            else{
                                toStmt.setObject(i, resultSet.getObject(i));
                            }
                        }
                        else{
                            toStmt.setObject(i, resultSet.getObject(i));
                        }
                    }
                    toStmt.addBatch();
                    count++;
                    if(count % 1000 == 0) {
                        toStmt.executeBatch();
                        toConn.commit();
                        count = 0;
                    }
                }

                // 处理剩余不足1000条的记录
                if (count > 0) {
                    toStmt.executeBatch();
                    toConn.commit(); // 最后一批记录，即使不满1000也提交
                }

                //判断数据量是否相等
                try (PreparedStatement oracleStmt1 = sourceConn.prepareStatement(checkSql);
                     PreparedStatement mysqlStmt1 = toConn.prepareStatement(checkSql)) {
                    ResultSet resultSet1 = oracleStmt1.executeQuery();
                    ResultSet resultSet2 = mysqlStmt1.executeQuery();
                    int count1 = 0;
                    int count2 = 0;
                    while (resultSet1.next()) {
                        count1 = resultSet1.getInt(1);
                    }
                    while (resultSet2.next()) {
                        count2 = resultSet2.getInt(1);
                    }
                    if(count1 != count2){
                        System.err.println(tableName + "数据量不相等，请检查.源库有：" + count1 + "，目标库有：" + count2);
                    } else{
                        System.out.println( tableName + "数据量相等，请检查.源库有：" + count1 + "，目标库有：" + count2);
                    }
                }
                long step2 = System.currentTimeMillis();
                System.out.println("表 " + tableName + " 数据迁移完毕. 耗时：" + (step2 - step1) + "毫秒");
            } catch (IOException e) {
                e.printStackTrace();
            }

        }
    }

    /**
     * oracle to mysql(表字段类型转换)
     * @param oracleType oracle字段类型
     * @return
     */
    private String translateColumnTypeFromOracleToMysql(String oracleType,int dataSize,int decimalDigits,int radix,String remarks,String nullable,String defaultValue) {
        if(remarks == "" || remarks == null){
            remarks = "";
        }
        switch (oracleType.toUpperCase()) {
            case "VARCHAR2":
            case "VARCHAR":
            case "NVARCHAR2":
                String v_varcharType = "";
                if(dataSize < 255) {
                    v_varcharType = OracleColumnType.getMsqlTypeByOracleType(oracleType) + "(" + dataSize + ") ";
                    if (nullable.trim().length() > 0) {
                        //非空
                        v_varcharType = v_varcharType + nullable;
                        if (defaultValue == null || defaultValue == "") {
                            //非空的字段default值如果没有就不需要写了
                        } else if ("sys_guid()".equals(defaultValue.trim())) {
                            v_varcharType = v_varcharType + " DEFAULT sys_guid() ";
                        } else {
                            v_varcharType = v_varcharType + " DEFAULT '" + defaultValue + "' ";
                        }
                    } else {
                        v_varcharType = v_varcharType + " NULL DEFAULT " + defaultValue;
                    }
                    v_varcharType = v_varcharType + " COMMENT '" + remarks + "'";
                }else {
                    v_varcharType = OracleColumnType.getMsqlTypeByOracleType("TEXT") ;
                    if (nullable.trim().length() > 0) {
                        //非空
                        v_varcharType = v_varcharType + nullable;
                        if (defaultValue == null || defaultValue == "") {
                            //非空的字段default值如果没有就不需要写了
                        } else if ("sys_guid()".equals(defaultValue.trim())) {
                            v_varcharType = v_varcharType + " DEFAULT sys_guid() ";
                        } else {
                            v_varcharType = v_varcharType + " DEFAULT '" + defaultValue + "' ";
                        }
                    } else {
                        v_varcharType = v_varcharType + " NULL DEFAULT " + defaultValue;
                    }
                    v_varcharType = v_varcharType + " COMMENT '" + remarks + "'";
                }
                return v_varcharType;
            case "INTEGER":
                String v_intType  = OracleColumnType.getMsqlTypeByOracleType(oracleType) ;
                if(nullable.trim().length() > 0){
                    //非空
                    v_intType = v_intType + nullable ;
                    if(defaultValue == null || defaultValue == ""){
                        //非空的字段default值如果没有就不需要写了
                    }else {
                        v_intType = v_intType + " DEFAULT '" + defaultValue + "' " ;
                    }
                }else {
                     v_intType = v_intType + " NULL DEFAULT " + defaultValue ;
                }
                v_intType = v_intType + " COMMENT '" + remarks + "'";
                return v_intType;
            case "NUMBER":
                String v_numberType = "" ;
                //数据类型转换
                if(decimalDigits>0 && radix > 0){
                    //预期转换的是oralce中NUMBER(20,5)，转换成mysql中的decimal(20,5)或者double(20, 5)
                    v_numberType = v_numberType + OracleColumnType.getMsqlTypeByOracleType(oracleType) + "(" + decimalDigits + "," + radix + ")";
                }else if(decimalDigits>8 && radix == 0){
                    //预期转换的是oralce中NUMBER(20)，转换成mysql中的bigint(20)
                    v_numberType = v_numberType + OracleColumnType.getMsqlTypeByOracleType("NUMBER_BIGINT") + "(" + decimalDigits + ")";
                }else {
                    // //预期转换的是oralce中NUMBER，转换成mysql中的int
                    v_numberType = v_numberType + OracleColumnType.getMsqlTypeByOracleType("NUMBER_INT") ;
                }
                //默认值和是否非空转换
                if(nullable.trim().length() > 0){
                    //非空
                    v_numberType = v_numberType + nullable ;
                    if(defaultValue == null || defaultValue == ""){
                        //非空的字段default值如果没有就不需要写了
                    } else {
                        v_numberType = v_numberType + " DEFAULT '" + defaultValue + "' " ;
                    }
                }else {
                    v_numberType = v_numberType + " NULL DEFAULT " + defaultValue ;
                }
                //列注释
                v_numberType = v_numberType + " COMMENT '" + remarks + "'";
                return v_numberType;
            case "DATE":
            case "TIMESTAMP":
                String v_dateType = OracleColumnType.getMsqlTypeByOracleType(oracleType);
                if(nullable.trim().length() > 0){
                    v_dateType = v_dateType  + " " + nullable;
                    if(defaultValue == null || defaultValue == ""){
                        //非空的字段default值如果没有就不需要写了
                    } else if (defaultValue.trim().length() > 0 ) {
                        v_dateType = v_dateType + " DEFAULT NOW()  " ;
                    }
                }else{
                    v_dateType = v_dateType + " NULL ";
                }

                v_dateType = v_dateType + " COMMENT '" + remarks + "'";
                return v_dateType;
            case "CHAR":
            case "BINARY_FLOAT":
            case "FLOAT":
            case "BINARY_DOUBLE":
                if(nullable.trim().length() > 0){
                    //非空
                    return OracleColumnType.getMsqlTypeByOracleType(oracleType) + "(" + dataSize + ") " + nullable + " COMMENT '" + remarks + "'";
                }else {
                    return OracleColumnType.getMsqlTypeByOracleType(oracleType) + "(" + dataSize + ") NULL DEFAULT " + defaultValue + " COMMENT '" + remarks + "'";
                }
            case "BLOB":
            case "BFILE":
            case "CLOB":
            case "LONG":
            case "NCLOB":
            case "RAW":
            case "SMALLINT":
                if(nullable.trim().length() > 0){
                    //非空
                    return OracleColumnType.getMsqlTypeByOracleType(oracleType) + nullable + " COMMENT '" + remarks + "'";
                }else {
                    return OracleColumnType.getMsqlTypeByOracleType(oracleType) +  " NULL DEFAULT " + defaultValue + " COMMENT '" + remarks + "'";
                }
            default:
                throw new IllegalArgumentException("Unsupported Oracle data type: " + oracleType);
        }
    }
    /**
     * oracle to postgresql (表字段类型转换)
     * @param oracleType oracle字段类型
     * @return
     */
    private String translateColumnTypeFromOracleToPostgresql(String oracleType,int dataSize,int decimalDigits,int radix,String nullable,String defaultValue,String toType) {
        //参考 https://www.cnblogs.com/mm0712/p/17579641.html
        //参考 https://blog.csdn.net/weixin_33918114/article/details/90626933
        //oracle传入的参数有一个是timestap(6),
        if("TIMESTAMP(6)".equals(oracleType)){
            oracleType = "TIMESTAMP";
        }
        switch (oracleType.toUpperCase()) {
            case "VARCHAR2":
            case "VARCHAR":
            case "NVARCHAR2":
                String v_varcharType = "";
                dataSize = dataSize * 3;
                if(dataSize <= 4000) {
                    v_varcharType = OracleColumnType.getPostgresTypeByOracleType(oracleType) + "(" + dataSize + ") ";
                    if (nullable.trim().length() > 0) {
                        //非空
                        v_varcharType = v_varcharType + nullable;
                        if (defaultValue == null || defaultValue == "") {
                            //非空的字段default值如果没有就不需要写了
                        } else if ("sys_guid()".equals(defaultValue.trim()) && "Postgres".equals(toType)) {
                            //默认值是sys_guid(),好像pg版本不支持这个函数，暂时也没找到合适的替代函数，这个默认值就先不写吧
                            v_varcharType = v_varcharType + " DEFAULT uuid_generate_v4()";
                        } else if("sys_guid()".equals(defaultValue.trim()) && "Vastbase".equals(toType)){
                            v_varcharType = v_varcharType + " DEFAULT uuid()";
                        } else {
                            if(defaultValue.contains("'") ){
                                v_varcharType = v_varcharType + " DEFAULT " + defaultValue ;
                            }else {
                                v_varcharType = v_varcharType + " DEFAULT '" + defaultValue + "' ";
                            }
                        }
                    } else {
                        if(defaultValue != null && defaultValue != ""){
                            if ("sys_guid()".equals(defaultValue.trim()) ) {
                                //默认值是sys_guid(),好像pg版本不支持这个函数，暂时也没找到合适的替代函数，这个默认值就先不写吧
                            }
                        } else {
                            v_varcharType = v_varcharType + " NULL DEFAULT " + defaultValue;
                        }
                    }
                }else {
                    v_varcharType = OracleColumnType.getPostgresTypeByOracleType("TEXT");
                    if (nullable.trim().length() > 0) {
                        //非空
                        v_varcharType = v_varcharType + nullable;
                        if (defaultValue == null || defaultValue == "") {
                            //非空的字段default值如果没有就不需要写了
                        } else if ("sys_guid()".equals(defaultValue.trim())) {
                            //默认值是sys_guid(),好像pg版本不支持这个函数，暂时也没找到合适的替代函数，这个默认值就先不写吧
//                            v_varcharType = v_varcharType + " DEFAULT sys_guid() ";
                        } else {
                            v_varcharType = v_varcharType + " DEFAULT '" + defaultValue + "' ";
                        }
                    } else {
                        v_varcharType = v_varcharType + " NULL DEFAULT " + defaultValue;
                    }
                }
                return v_varcharType;
            case "INTEGER":
                String v_intType  = OracleColumnType.getPostgresTypeByOracleType(oracleType) ;
                if(nullable.trim().length() > 0){
                    //非空
                    v_intType = v_intType + nullable ;
                    if(defaultValue == null || defaultValue == ""){
                        //非空的字段default值如果没有就不需要写了
                    }else {
                        v_intType = v_intType + " DEFAULT '" + defaultValue + "' " ;
                    }
                }else {
                    v_intType = v_intType + " NULL DEFAULT " + defaultValue ;
                }
                return v_intType;
            case "NUMBER":
                String v_numberType = " " ;
                //数据类型转换
                if(decimalDigits>0 && radix > 0){
                    //预期转换的是oralce中NUMBER(20,5)，转换成mysql中的decimal(20,5)或者double(20, 5)
                    v_numberType = v_numberType + OracleColumnType.getPostgresTypeByOracleType(oracleType) + "(" + decimalDigits + "," + radix + ")";
                }else if(decimalDigits <= 9 && radix == 0){
                    //预期转换的是oralce中NUMBER(20)，转换成mysql中的bigint(20)
                    v_numberType = v_numberType + OracleColumnType.getPostgresTypeByOracleType("INTEGER");
                }else if(decimalDigits > 9  && decimalDigits <= 18 && radix == 0) {
                    // //预期转换的是oralce中NUMBER，转换成mysql中的int
                    v_numberType = v_numberType + OracleColumnType.getPostgresTypeByOracleType("NUMBER_BIGINT") ;
                }else if(decimalDigits > 18 && radix == 0){
                    v_numberType = v_numberType + OracleColumnType.getPostgresTypeByOracleType(oracleType) +"(" + decimalDigits + ",0)" ;
                }else {
                    v_numberType = v_numberType + OracleColumnType.getPostgresTypeByOracleType("INTEGER");
                }
                //默认值和是否非空转换
                if(nullable.trim().length() > 0){
                    //非空
                    v_numberType = v_numberType + nullable ;
                    if(defaultValue == null || defaultValue == ""){
                        //非空的字段default值如果没有就不需要写了
                    } else {
                        v_numberType = v_numberType + " DEFAULT " + defaultValue + " " ;
                    }
                }else {
                    if(defaultValue == null || defaultValue.contains("")){
                        //非空的字段default值如果没有就不需要写了
                        //defaultValue.contains("") 是因为有表的默认值写的是''\n
                    } else {
                        v_numberType = v_numberType + " DEFAULT " + defaultValue  ;
                    }
                }
                return v_numberType;
            case "DATE":
            case "TIMESTAMP":
                String v_dateType = OracleColumnType.getPostgresTypeByOracleType(oracleType);
                if(nullable.trim().length() > 0){
                    v_dateType = v_dateType  + nullable;
                    if(defaultValue == null || defaultValue == ""){
                        //非空的字段default值如果没有就不需要写了
                    } else if (defaultValue.trim().length() > 0 ) {
                        v_dateType = v_dateType + " DEFAULT NOW()  " ;
                    }
                }else{
                    v_dateType = v_dateType + " NULL ";
                }
                return v_dateType;
            case "CHAR":
            case "NCHAR":
            case "BINARY_FLOAT":
            case "FLOAT":
                if(nullable.trim().length() > 0){
                    //非空
                    return OracleColumnType.getPostgresTypeByOracleType(oracleType) + "(" + dataSize + ") " + nullable ;
                }else {
                    return OracleColumnType.getPostgresTypeByOracleType(oracleType) + "(" + dataSize + ") NULL DEFAULT " + defaultValue ;
                }
            case "SMALLINT":
            case "BINARY_DOUBLE":
            case "BLOB":
            case "RAW":
            case "CLOB":
            case "LONG":
            case "NCLOB":
            case "BFILE":
                if(nullable.trim().length() > 0){
                    //非空
                    return OracleColumnType.getPostgresTypeByOracleType(oracleType) + " " + nullable + defaultValue;
                }else {
                    return OracleColumnType.getPostgresTypeByOracleType(oracleType) + " NULL DEFAULT " + defaultValue ;
                }
            default:
                throw new IllegalArgumentException("Unsupported Oracle data type: " + oracleType);
        }
    }


    private String translateColumnTypeFromMysqlToPostgresql(String fromColumnType, Long fromDataSize, int fromDecimalDigits, int fromRadix,
                                                            String nullable, String defaultValue) {
       //资料参考：https://www.cnblogs.com/wangsongbai/p/13444600.html
        String v_returnType = "";
        fromColumnType = fromColumnType.toUpperCase();
        switch (fromColumnType) {
            case "VARCHAR":
                //在迁移数据过程中发现mysql中比如varchar（200）的字段写入的数据长度length超过200，vastbase写入不进去，需要修改字段长度
                //根据海量工程师反馈这是跟字符集有关 uft8的字符集，一个汉字占3个字节，如果是gbk的字符集，可以就可以存20个汉字民
                fromDataSize = fromDataSize * 3;
                if(fromDataSize ==0){
                    fromDataSize = Long.valueOf(50);
                }
                if(fromDataSize <= 4000) {
                    v_returnType = MysqlColumnType.getPostgresType(fromColumnType) + "(" + fromDataSize + ") ";
                    if (nullable.trim().length() > 0) {
                        //非空
                        v_returnType = v_returnType + nullable;
                        if (defaultValue == null || defaultValue == "") {
                            //非空的字段default值如果没有就不需要写了
                        } else if (defaultValue.trim().contains("guid") || defaultValue.trim().contains("uuid")) {
                            //默认值是sys_guid(),好像pg版本不支持这个函数，暂时也没找到合适的替代函数，这个默认值就先不写吧
//                            v_varcharType = v_varcharType + " DEFAULT sys_guid() ";
                        } else {
                            v_returnType = v_returnType + " DEFAULT " + defaultValue + " ";
                        }
                    } else {
                        if(defaultValue != null && defaultValue != ""){
                            if ("sys_guid()".equals(defaultValue.trim()) ) {
                                //默认值是sys_guid(),好像pg版本不支持这个函数，暂时也没找到合适的替代函数，这个默认值就先不写吧
//                            v_varcharType = v_varcharType + " DEFAULT sys_guid() ";
                            }
                        } else {
                            v_returnType = v_returnType + " NULL DEFAULT " + defaultValue;
                        }
                    }
                }else {
                    v_returnType = MysqlColumnType.getPostgresType("VARCHAR_TEXT");
                    if (nullable.trim().length() > 0) {
                        //非空
                        v_returnType = v_returnType + nullable;
                        if (defaultValue == null || defaultValue == "") {
                            //非空的字段default值如果没有就不需要写了
                        } else if ("sys_guid()".equals(defaultValue.trim())) {
                            //默认值是sys_guid(),好像pg版本不支持这个函数，暂时也没找到合适的替代函数，这个默认值就先不写吧
//                            v_varcharType = v_varcharType + " DEFAULT sys_guid() ";
                        } else {
                            v_returnType = v_returnType + " DEFAULT '" + defaultValue + "' ";
                        }
                    } else {
                        v_returnType = v_returnType + " NULL DEFAULT " + defaultValue;
                    }
                }
                break;
            case "LONGTEXT":
            case "TEXT":
                v_returnType = MysqlColumnType.getPostgresType(fromColumnType) ;
                if (nullable.trim().length() > 0) {
                    //非空
                    v_returnType = v_returnType + nullable;
                    if (defaultValue == null || defaultValue == "") {
                        //非空的字段default值如果没有就不需要写了
                    } else if ("sys_guid()".equals(defaultValue.trim())) {
                        //默认值是sys_guid(),好像pg版本不支持这个函数，暂时也没找到合适的替代函数，这个默认值就先不写吧
                    } else {
                        v_returnType = v_returnType + " DEFAULT '" + defaultValue + "' ";
                    }
                } else {
                    v_returnType = v_returnType + " NULL DEFAULT " + defaultValue;
                }
                break;
            case "TINYINT":
            case "INT":
            case "BIGINT":
            case "SMALLINT":
            case "BINARY_DOUBLE":
            case "LONGBLOB":
            case "RAW":
            case "BLOB":
            case "CLOB":
            case "LONG":
            case "NCLOB":
            case "MEDIUMTEXT":
            case "BFILE":
            case "BIT":
                v_returnType  = MysqlColumnType.getPostgresType(fromColumnType);
                if(nullable.trim().length() > 0){
                    //非空
                    v_returnType = v_returnType + " "+ nullable ;
                    if(defaultValue == null || defaultValue == ""){
                        //非空的字段default值如果没有就不需要写了
                    }else {
                        v_returnType = v_returnType + " DEFAULT " + defaultValue  ;
                    }
                }else {
                    v_returnType = v_returnType + " NULL DEFAULT " + defaultValue ;
                }
               break;
            case "CHAR":
                v_returnType = MysqlColumnType.getPostgresType(fromColumnType) + "("+ fromDataSize +")";
                if(nullable.trim().length() > 0){
                    //非空
                    v_returnType = v_returnType + nullable ;
                    if(defaultValue == null || defaultValue == ""){
                        //非空的字段default值如果没有就不需要写了
                    }else {
                        v_returnType = v_returnType + " DEFAULT '" + defaultValue + "' ";
                    }
                }else {
                }
                return v_returnType;
            case "DATE":
            case "DATETIME":
            case "TIMESTAMP":
                v_returnType = MysqlColumnType.getPostgresType(fromColumnType);
                if(nullable.trim().length() > 0){
                    //非空
                    v_returnType = v_returnType + " "+ nullable ;

                }else {
                        v_returnType = v_returnType + " NULL "   ;
                }
                if(defaultValue == null || defaultValue == ""){
                    //非空的字段default值如果没有就不需要写了
                    v_returnType = v_returnType + " DEFAULT null "   ;
                }else if(defaultValue.contains("current_timestamp()")){
                    v_returnType = v_returnType + " DEFAULT now()"   ;
                }else{
                    v_returnType = v_returnType + " DEFAULT now()";
                }
                break;
            case "DOUBLE":
            case "DECIMAL":
                v_returnType = " " ;
                //数据类型转换
                if(fromDecimalDigits>0 && fromRadix > 0){
                    //预期转换的是mysql中的decimal(20,5)或者double(20, 5)
                    v_returnType = v_returnType + MysqlColumnType.getPostgresType(fromColumnType) +"(" + fromDecimalDigits + "," + fromRadix + ")";
                }
                //默认值和是否非空转换
                if(nullable.trim().length() > 0){
                    //非空
                    v_returnType = MysqlColumnType.getPostgresType(fromColumnType) + "(" + fromDecimalDigits + "," + fromRadix + ")" + nullable ;
                    if(defaultValue == null || defaultValue == ""){
                        //非空的字段default值如果没有就不需要写了
                    } else {
                        v_returnType = MysqlColumnType.getPostgresType("FLOAT8") + " DEFAULT '" + defaultValue + "' " ;
                    }
                }else {
                    v_returnType = MysqlColumnType.getPostgresType("FLOAT8") +" NULL DEFAULT " + defaultValue ;
                }
                break;
            case "BINARY":
            case "FLOAT":
                if(fromRadix > 0) {
                    if (nullable.trim().length() > 0) {
                        //非空
                        v_returnType = MysqlColumnType.getPostgresType("FLOAT_PRECISION") + "(" + fromDecimalDigits + ","+ fromRadix +") " + nullable;
                    } else {
                        v_returnType = MysqlColumnType.getPostgresType("FLOAT_PRECISION") + "(" + fromDecimalDigits + ","+ fromRadix +")  NULL DEFAULT " + defaultValue;
                    }
                }else{
                    if (nullable.trim().length() > 0) {
                        //非空
                        v_returnType = MysqlColumnType.getPostgresType(fromColumnType) + "(" + fromDecimalDigits + ") " + nullable;
                    } else {
                        v_returnType = MysqlColumnType.getPostgresType(fromColumnType) + "(" + fromDecimalDigits + ") NULL DEFAULT " + defaultValue;
                    }
                }
                break;
            case "ENUM":
                return MysqlColumnType.getPostgresType(fromColumnType) + "(64) ";
            default:
                throw new IllegalArgumentException("未设置的Mysql类型转换: " + fromColumnType);
        }
        return v_returnType;
    }

    private String translateColumnTypeFromPostgresToPostgresql(String fromColumnType, int fromDataSize, int fromDecimalDigits, int i, String nullable, String defaultValue) {
        String v_ColumnType = fromColumnType;

        if(defaultValue != null && defaultValue != "") {
            //这是为了处理获取到的默认值增加了前后单引号：'''0''::smallint' --->实际上应该是 0::smallint 只是这样处理不知道会不会影响其他默认值
//            defaultValue = defaultValue.replace("'", "");
//            //处理默认值为 (0)::character varying
//            defaultValue = defaultValue.replace("(", "").replace(")", "");
            //处理部分特殊的默认值
            if (defaultValue.contains("guid") || defaultValue.contains("uuid") || defaultValue.contains("NULL") || defaultValue.contains("nextval")) {
                defaultValue = "";
            }else{
                defaultValue = " DEFAULT " + defaultValue ;
            }
        }else{
            defaultValue = "";
        }
        switch (fromColumnType.toUpperCase()) {
            case "UUID":
                v_ColumnType =  "VARCHAR(255) " ;
                if (nullable.trim().length() > 0) {
                    //非空
                    v_ColumnType = v_ColumnType + nullable;
                } else {
                    v_ColumnType = v_ColumnType + " NULL  " ;
                }
                v_ColumnType = v_ColumnType +  defaultValue ;

                return v_ColumnType;
            case "VARCHAR":
                if(fromDataSize == 0){
                    fromDataSize = 255;
                }else{
                    fromDataSize = fromDataSize * 3;
                }
                v_ColumnType =  v_ColumnType + "(" + fromDataSize + ") ";
                if (nullable.trim().length() > 0) {
                    //非空
                    v_ColumnType = v_ColumnType + nullable;
                } else {
                    v_ColumnType = v_ColumnType + " NULL  " ;
                }
                v_ColumnType = v_ColumnType +  defaultValue ;

                return v_ColumnType;
            case "NUMERIC":
                fromDecimalDigits = fromDecimalDigits==0 ? 1 : fromDecimalDigits;
                v_ColumnType = v_ColumnType + "(" + fromDecimalDigits + "," + fromDataSize + ") ";
                if (nullable.trim().length() > 0) {
                    //非空
                    v_ColumnType = v_ColumnType + nullable;
                } else {
                    v_ColumnType = v_ColumnType + " NULL  " ;
                }
                return v_ColumnType + defaultValue;
            case "INT8":
                if(nullable.trim().length() > 0){
                    //非空
                    v_ColumnType = v_ColumnType + " " +  nullable ;
                }
                return v_ColumnType + defaultValue;
            case "INT2":
            case "INT4":
            case "FLOAT8":
            case "FLOAT4":
            case "BIT":
            case "JSON":
                if(nullable.trim().length() > 0){
                    //非空
                    v_ColumnType = v_ColumnType + nullable ;
                }else {
                    v_ColumnType = v_ColumnType + " NULL  "  ;
                }
                return v_ColumnType + defaultValue;
            case "DATE":
            case "TIMESTAMP":
            case "TIMESTAMPTZ":
                v_ColumnType = " TIMESTAMP(6) ";
                if(nullable.trim().length() > 0){
                    v_ColumnType = v_ColumnType  + nullable;
                    if(defaultValue == null || defaultValue == ""){
                        //非空的字段default值如果没有就不需要写了
                    } else if (defaultValue.trim().length() > 0 ) {
                        v_ColumnType = v_ColumnType + " DEFAULT NOW()  " ;
                    }
                }else{
                    v_ColumnType = v_ColumnType + " NULL " ;
                }
                return v_ColumnType;
            case "CHAR":
            case "BPCHAR":
            case "NCHAR":
                if(nullable.trim().length() > 0){
                    //非空
                    return v_ColumnType + "(" + fromDataSize + ") " + nullable ;
                }else {
                    return v_ColumnType + "(" + fromDataSize + ") NULL " + defaultValue ;
                }
            case "TEXT":
            case "BOOL":
            case "BYTEA":
                if(nullable.trim().length() > 0){
                    //非空
                    return v_ColumnType + nullable ;
                }else {
                    return v_ColumnType +" NULL " + defaultValue ;
                }
            default:
                throw new IllegalArgumentException("此列postgres类型未转换: " + fromColumnType);
        }
    }

    private String translateColumnTypeFromPostgresToMysql(String fromColumnType, int fromDataSize, int fromDecimalDigits, int i, String nullable, String defaultValue) {
        fromColumnType = fromColumnType.toUpperCase(Locale.ROOT);
        switch (fromColumnType) {
            case "VARCHAR2":
            case "VARCHAR":
            case "NVARCHAR2":
                String v_varcharType = "";
                if(fromDataSize < 1000) {
                    v_varcharType = PostgresColumnType.getMysqlType(fromColumnType) + "(" + fromDataSize + ") ";
                    if (nullable.trim().length() > 0) {
                        //非空
                        v_varcharType = v_varcharType + nullable;
                        if (defaultValue == null || defaultValue == "") {
                            //非空的字段default值如果没有就不需要写了
                        } else if ("sys_guid()".equals(defaultValue.trim())) {
                            v_varcharType = v_varcharType + " DEFAULT sys_guid() ";
                        } else {
                            v_varcharType = v_varcharType + " DEFAULT " + defaultValue ;
                        }
                    } else {
                        v_varcharType = v_varcharType + " NULL DEFAULT " + defaultValue;
                    }
                }else {
                    v_varcharType = PostgresColumnType.getMysqlType("TEXT") ;
                    if (nullable.trim().length() > 0) {
                        //非空
                        v_varcharType = v_varcharType + nullable;
                        if (defaultValue == null || defaultValue == "") {
                            //非空的字段default值如果没有就不需要写了
                        } else if ("sys_guid()".equals(defaultValue.trim())) {
                            v_varcharType = v_varcharType + " DEFAULT sys_guid() ";
                        } else {
                            v_varcharType = v_varcharType + " DEFAULT '" + defaultValue + "' ";
                        }
                    } else {
                        v_varcharType = v_varcharType + " NULL DEFAULT " + defaultValue;
                    }
                }
                return v_varcharType;
            case "INT8":
            case "INT4":
            case "INT2":
                String v_intType  = PostgresColumnType.getMysqlType(fromColumnType) ;
                if(nullable.trim().length() > 0){
                    //非空
                    v_intType = v_intType + nullable ;
                    if(defaultValue == null || defaultValue == ""){
                        //非空的字段default值如果没有就不需要写了
                    }else {
                        v_intType = v_intType + " DEFAULT " + defaultValue;
                    }
                }else {
                    v_intType = v_intType + " NULL DEFAULT " + defaultValue ;
                }
                return v_intType;
            case "NUMBER":
            case "NUMERIC":
                String v_numberType = "" ;
                //数据类型转换
                if(fromDataSize>0 && fromDecimalDigits > 0){
                    //预期转换的是oralce中NUMBER(20,5)，转换成mysql中的decimal(20,5)或者double(20, 5)
                    v_numberType = v_numberType + PostgresColumnType.getMysqlType(fromColumnType) + "(" + fromDataSize + "," + fromDecimalDigits + ")";
                }else if(fromDecimalDigits>8 && fromDataSize == 0){
                    //预期转换的是oralce中NUMBER(20)，转换成mysql中的bigint(20)
                    v_numberType = v_numberType + PostgresColumnType.getMysqlType("NUMBER_BIGINT") ;
                }else {
                    // //预期转换的是oralce中NUMBER，转换成mysql中的int
                    v_numberType = v_numberType + PostgresColumnType.getMysqlType("NUMBER_INT") ;
                }
                //默认值和是否非空转换
                if(nullable.trim().length() > 0){
                    //非空
                    v_numberType = v_numberType + nullable ;
                    if(defaultValue == null || defaultValue == ""){
                        //非空的字段default值如果没有就不需要写了
                    } else {
                        v_numberType = v_numberType + " DEFAULT '" + defaultValue + "' " ;
                    }
                }else {
                    v_numberType = v_numberType + " NULL DEFAULT " + defaultValue ;
                }
                return v_numberType;
            case "DATE":
            case "TIMESTAMP":
            case "TIMESTAMPTZ":
                String v_dateType = PostgresColumnType.getMysqlType(fromColumnType);
                if(nullable.trim().length() > 0){
                    v_dateType = v_dateType  + " " + nullable;
                    if(defaultValue == null || defaultValue == ""){
                        //非空的字段default值如果没有就不需要写了
                    } else if (defaultValue.trim().length() > 0 ) {
                        v_dateType = v_dateType + " DEFAULT NOW()  " ;
                    }
                }else{
                    v_dateType = v_dateType + " NULL ";
                }

                return v_dateType;
            case "CHAR":
            case "BPCHAR":
            case "BINARY_FLOAT":
            case "FLOAT":
            case "FLOAT4":
            case "FLOAT8":
            case "BINARY_DOUBLE":
                if(nullable.trim().length() > 0){
                    //非空
                    return PostgresColumnType.getMysqlType(fromColumnType) + "(" + fromDataSize + ") " + nullable ;
                }else {
                    return PostgresColumnType.getMysqlType(fromColumnType) + "(" + fromDataSize + ") NULL DEFAULT " + defaultValue ;
                }
            case "BLOB":
            case "TEXT":
            case "BFILE":
            case "BYTEA":
            case "JSON":
            case "CLOB":
            case "LONG":
            case "NCLOB":
            case "BOOL":
            case "RAW":
            case "SMALLINT":
                if(nullable.trim().length() > 0){
                    //非空
                    return PostgresColumnType.getMysqlType(fromColumnType) + nullable ;
                }else {
                    return PostgresColumnType.getMysqlType(fromColumnType) +  " NULL DEFAULT " + defaultValue ;
                }
            case "UUID":
                return PostgresColumnType.getMysqlType(fromColumnType) + "(64)" + nullable ;
            default:
                throw new IllegalArgumentException("未支持postgres 到 mysql 的类型转换: " + fromColumnType);
        }
    }

}
